Re: st: Find the fiscal year for each obs

["Yu Chen, PhD" <profyuchen@gmail.com>]

Nick and Tom,
Thank you so very much for your advice, which is indeed very helpful.




On Sat, Apr 6, 2013 at 9:45 AM, Tom <tommedema@gmail.com> wrote:
> Dear Yu Chen,
>
> It might be that the discussion at
> http://www.nber.org/sys-admin/large-stata-datasets.html is helpful to
> you. It specifically addresses large datasets.
>
> Kind regards,
> Tom
>
> On Sat, Apr 6, 2013 at 4:24 PM, Yu Chen, PhD <profyuchen@gmail.com> wrote:
>> Nick,
>> Thank you so much for the code. It works great. However, I have a
>> further question. Assume File A has 5000 firms, and each has 360 daily
>> stock price observations for 5 years, then there are 9,000,000
>> observations in File A. Assume File B has 5 years data (i.e., fiscal
>> year definition) for each firm, then there are 25,000 observations in
>> File B. If I use -merge-, the resulting file will be too large for the
>> computer to handle. So I think some kind of loop structure might need
>> to be used. Or there may be a better solution. Could you please help
>> me in this regard? What code will be good to handle this problem?
>> Best,
>>
>> Yu Chen
>>
>>
>>
>>
>> On Sat, Apr 6, 2013 at 6:50 AM, Nick Cox <njcoxstata@gmail.com> wrote:
>>> I'd just -merge- the files. The convention seems to be that Fiscal
>>> Years are labelled by the year they start in. (It's _always_ a good
>>> idea to explain stuff that won't be universally understood; assuming
>>> that people work in your field is usually wrong.) Either way, there's
>>> a correction for dates that are in calendar years that differ from
>>> fiscal years. This example uses slightly different fake data. I used
>>> an ancient -merge- only because I am writing from an ancient machine.
>>> (I have Stata 12, but this machine doesn't read DVDs.)
>>>
>>> clear
>>> input str3 Firm str10 Date
>>> IBM    "2009/01/15"
>>> IBM    "2010/01/16"
>>> 3M     "2010/07/01"
>>> 3M     "2011/07/02"
>>> end
>>> gen Ndate = date(Date, "YMD")
>>> gen Fiscal = year(Ndate)
>>> sort Firm Fiscal
>>> save FileA, replace
>>>
>>> clear
>>> input str3 Firm  Fiscal  str10 Begin str10 End
>>> IBM    2009               "2009/03/01"   "2010/02/28"
>>> IBM    2010               "2010/03/01"   "2011/02/28"
>>> 3M    2010               "2010/06/01"    "2011/05/31"
>>> 3M     2011               "2011/06/01"     "2012/05/31"
>>> end
>>>
>>> gen Nbegin = date(Begin, "YMD")
>>> gen Nend = date(End, "YMD")
>>> sort Firm Fiscal
>>>
>>> version 10: merge Firm Fiscal using FileA
>>>
>>> replace Fiscal = Fiscal - 1 if !inrange(Ndate, Nbegin, Nend)
>>> drop _merge
>>> list Firm Fiscal Begin End Date
>>>
>>>
>>> Nick
>>> njcoxstata@gmail.com
>>>
>>>
>>> On 6 April 2013 12:15, Yu Chen, PhD <profyuchen@gmail.com> wrote:
>>>
>>>> I have two files. File A contains the daily stock price for each firm,
>>>> and file B contains the beginning and ending dates for a fiscal year
>>>> for each firm. See below for an illustration (data are hypothetical):
>>>>
>>>> File A:
>>>> Firm   Date                Price
>>>> IBM    2009/01/15       xxxx
>>>> IBM    2009/01/16       xxxx
>>>> ...................
>>>> 3M     2010/07/01       yyyy
>>>> 3M     2010/07/02       yyyy
>>>> ...................
>>>>
>>>> File B:
>>>> Firm   Fiscal Year      Beg. Date    End. Date
>>>> IBM    2009               2009/03/01   2010/02/28
>>>> IBM    2010               2010/03/01   2011/02/28
>>>> ..................
>>>> 3M     2010               2010/06/01    2011/05/31
>>>> 3M     2011               2011/06/01     2012/05/31
>>>> ..................
>>>>
>>>> Now, I want indicate in File A for each observation the fiscal year
>>>> the observation belongs to, according to the definition in File B.
>>>> Conceptually it seems easy: Just compare the date of the observation
>>>> in File A with the beginning and ending dates in File B.
>>> *
>>> *   For searches and help try:
>>> *   http://www.stata.com/help.cgi?search
>>> *   http://www.stata.com/support/faqs/resources/statalist-faq/
>>> *   http://www.ats.ucla.edu/stat/stata/
>> *
>> *   For searches and help try:
>> *   http://www.stata.com/help.cgi?search
>> *   http://www.stata.com/support/faqs/resources/statalist-faq/
>> *   http://www.ats.ucla.edu/stat/stata/
> *
> *   For searches and help try:
> *   http://www.stata.com/help.cgi?search
> *   http://www.stata.com/support/faqs/resources/statalist-faq/
> *   http://www.ats.ucla.edu/stat/stata/
*
*   For searches and help try:
*   http://www.stata.com/help.cgi?search
*   http://www.stata.com/support/faqs/resources/statalist-faq/
*   http://www.ats.ucla.edu/stat/stata/