Notice: On March 31, it was **announced** that Statalist is moving from an email list to a **forum**. The old list will shut down on April 23, and its replacement, **statalist.org** is already up and running.

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

From |
"Yu Chen, PhD" <profyuchen@gmail.com> |

To |
statalist@hsphsun2.harvard.edu |

Subject |
Re: st: Find the fiscal year for each obs |

Date |
Sat, 6 Apr 2013 12:11:49 -0500 |

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/

**References**:**st: Find the fiscal year for each obs***From:*"Yu Chen, PhD" <profyuchen@gmail.com>

**Re: st: Find the fiscal year for each obs***From:*Nick Cox <njcoxstata@gmail.com>

**Re: st: Find the fiscal year for each obs***From:*"Yu Chen, PhD" <profyuchen@gmail.com>

**Re: st: Find the fiscal year for each obs***From:*Tom <tommedema@gmail.com>

- Prev by Date:
**Re: st: Identify Closest Subsequent Date** - Next by Date:
**st: ML program in Stata 12.1 for a nonlinear equation of the form Y=F(x)*G(z)** - Previous by thread:
**Re: st: Find the fiscal year for each obs** - Next by thread:
**Re: st: Find the fiscal year for each obs** - Index(es):