Notice: On April 23, 2014, Statalist moved from an email list to a forum, based at statalist.org.
[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]
Re: st: Merging annual data and monthly data few months after year end
From
Nick Cox <[email protected]>
To
"[email protected]" <[email protected]>
Subject
Re: st: Merging annual data and monthly data few months after year end
Date
Sat, 18 Jan 2014 11:25:25 +0000
Thanks for the extra details. The -merge- shouldn't produce duplicates
on -permno-, -yr- and -mth- because the whole point is to match
observations on those variables.
In running a block of code, Stata should stop at the first error, so
where does that occur precisely? The -tsset-? Or before?
Note that
egen timeid = group(yr mth)
won't work well with gaps and in any case is much less direct than
gen timeid = ym(yr, mth)
which will produce monthly dates.
There is generic advice in the FAQ
How do I deal with a report of repeated time values within panel?
Dealing with reports of repeated time values within panel
http://www.stata.com/support/faqs/data-management/repeated-time-values/index.html
Nick
[email protected]
On 18 January 2014 02:36, Abdalla, Ahmed <[email protected]> wrote:
> I get an error message that variable does not uniquely identify observations. I understand that the problem might be because in the ccm.dta (annual data) each observation id (permno) has accounting data for one month for each fiscal year, which is the month at which the fiscal year ends. In the other data set, crsp.dta , each observation id (permno) has stock price data for the 12 months in each year. I need to link the annual accounting data at the fiscal year end (in the month the fiscal year ends) to the stock price at the same months and three months thereafter.
> I read thread you mentioned before posting my question, but still can't figure out how to solve my problem?
> Any suggestions will be much appreciated!
>
>
>
> ________________________________________
> From: [email protected] <[email protected]> on behalf of Nick Cox <[email protected]>
> Sent: 17 January 2014 20:43
> To: [email protected]
> Subject: Re: st: Merging annual data and monthly data few months after year end
>
> "doesn't work" is not informative.
>
> However, the recent thread
> http://www.stata.com/statalist/archive/2014-01/msg00589.html raised a
> similar-sounding problem.
>
> Nick
> [email protected]
>
>
> On 17 January 2014 18:35, Abdalla, Ahmed <[email protected]> wrote:
>> Dear Statalist
>> I have two databases:
>> a) ccm.dta that includes "annual" accounting data at fiscal year end for a set of firms over 20 years (however, unbalanced).
>>
>> Permno (firm identifier) datadate yr mth x1 x2
>>
>> 10001 30june1988 1988 6
>> 10001 30june1989 1989 6
>> 10001 31december1990 1990 12
>> 10002 30april1988 1988 4
>> 10002 30april1989 1989 4
>> 10002 31may 1990 5
>>
>> Note that a company may change its fiscal year end. A company that reports accounting data at the end of June this year may report data at December in the next year (or sometimes at the same year).
>>
>> b) crsp.dta that includes "monthly"stock prices for the same set of firms over the same period (however, unbalanced)
>>
>> Permno (firm identifier) datadate yr mth x3(stockprice)
>> 10001 29january1988 1988 1
>> 10001 29february1988 1988 2
>> 10001 31march1988 1988 3
>> 10001 29april1988 1988 4
>> 10001 31may1988 1988 5
>> 10001 30june1988 1988 6
>> 10001 31july1988 1988 7
>> .... ....
>> .... ...31dec1988 1988 12
>>
>> 10001 29january1989 1989 1
>> 10001 29february1989 1989 2
>> 10001 31march1989 1989 3
>> 10001 29april1989 1989 4
>> 10001 31may1989 1989 5
>> 10001 30june1989 1989 6
>> 10001 31july1989 1989 7
>> .... ....
>> .... ...31dec1989 1989 8
>>
>> 10002 29january1988 1988 1
>> 10002 29february1988 1988 2
>> 10002 31march1988 1988 3
>> 10002 29april1988 1988 4
>> 10002 31may1988 1988 5
>> 10002 30june1988 1988 6
>> 10002 31july1988 1988 7
>> .... ....
>> .... ...31dec1989 1988 12
>>
>>
>> I want to merge both data sets and link annual accounting data in ccm.dta to stock prices reported at the end of fiscal year and three months after the end of the fiscal year. Here is the code I used:
>>
>> use ccm.dta
>> duplicates drop
>> format datadate %d
>> * yr and mth were not reported in the original data in both datasets, however I wrote them in my example above*
>> gen yr=year(datadate)
>> gen mth=month(datadate)
>> drop if permno==.
>> sort permno yr mth
>> save ccm_2.dta
>>
>> use crsp.dta,clear
>> duplicates drop
>> format date %d
>> gen yr=year(date)
>> gen mth=month(date)
>> drop if permno==.
>> sort permno yr mth
>> merge 1:1 permno yr mth using ccm_2.dta
>> egen firmid= group(permno)
>> egen timeid=group(yr mth)
>> tsset firmid timeid
>> gen prc3=f3.prc
>> keep if _merge==3
>>
>>
>> The code didn't work and gives error messages that I couldn't fix and especially when I "tsset" my panel.
>>
>> Would you please suggest a proper code to merge these two datasets and link x1 x2 (annual data) to x3 (stock prices) at fiscal year end and three months after fiscal year end ?
> *
> * 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/