RE: st: RE: Merge problem

[Erik Aadland <erikaadland@hotmail.com>]

Thanks Joe!

I tweaked your code a little bit, and now it works like a charm.

gen period_id=. 
expand 2 if year==1 
bys agency_id year: replace period_id=_n if year==1 
expand 2 if year==2 ;
bys agency_id year: replace period_id=2+_n if year==2 


As you can see, I added "year" in the bysort command.

Thanks again.

Sincerely,
Erik.


----------------------------------------
> From: jcanner1@jhmi.edu
> To: statalist@hsphsun2.harvard.edu
> Subject: RE: st: RE: Merge problem
> Date: Fri, 4 Oct 2013 14:10:54 +0000
>
> I was going to suggest something like this (since I didn't have the time or brain power to suggest something more elegant). Based on your clarification I would have said something like this:
>
> gen period_id=.
> expand 7 if year==1
> bys firm_id: replace period_id=_n if year==1
> expand 8 if year==2
> bys firm_id: replace period_id=8+_n if year==2
> etc.
>
> Since you only have 8 years, the brute force method is probably not bad. If you want to have the numbering for each year pick up from the previous year without having to specify the offset each time, that could probably be done, but perhaps someone else can think of it more readily than I can at the moment.
>
>
> -----Original Message-----
> From: owner-statalist@hsphsun2.harvard.edu [mailto:owner-statalist@hsphsun2.harvard.edu] On Behalf Of Erik Aadland
> Sent: Friday, October 04, 2013 9:26 AM
> To: statalist@hsphsun2.harvard.edu
> Subject: RE: st: RE: Merge problem
>
> Following up on Joe's code, I did the following:
>
> expand 2 if year == 2003 ;
> expand 2 if year == 2004 ;
>
> bys year agency_id: gen period_id=_n ;
>
> In this code, I can specify the correct number of periods for each year.
>
> However, the code starts counting periods in each year starting from 1.
> I would like to have the code continue the counting from the highest number in the previous year.
> In the simple example above, the periods should be 3 and 4 for year == 2004 above, not 1 and 2.
>
> Is there a way to accomplish this?
>
> Thanks,
>
> Erik.
>
>
>
> ----------------------------------------
>> From: erikaadland@hotmail.com
>> To: statalist@hsphsun2.harvard.edu
>> Subject: RE: st: RE: Merge problem
>> Date: Fri, 4 Oct 2013 12:10:32 +0000
>>
>> Dear Joe.
>>
>> Thank you for your suggestion.
>>
>> The number of periods is not completely fixed in dataset (B).
>> In year == 1, I have periods (1-8)
>> In year == 2, I have periods (9-17)
>> In year == 3, I have periods (18-28)
>> In year == 4, I have periods (29-39)
>> In year == 5, I have periods (40-50)
>> In year == 6, I have periods (51-61)
>> In year == 7, I have periods (62-72)
>> In year == 8, I have periods (73-75)
>>
>> So a total of 75 periods spread over 8 years in dataset (B).
>>
>> Sincerely,
>> Erik.
>>
>>
>>> From: jcanner1@jhmi.edu
>>> To: statalist@hsphsun2.harvard.edu
>>> Subject: st: RE: Merge problem
>>> Date: Fri, 4 Oct 2013 11:56:06 +0000
>>>
>>> Erik,
>>>
>>> If there are a fixed, known number of periods per year, you can use -expand- in dataset (A). Suppose (as in your example) there are 4 periods (1,2,3,4):
>>>
>>> . expand 3
>>> . bys year firm_id: gen period_id=_n
>>>
>>> However, if the number of periods depends on some information in dataset (B), that's a different story. If so, please provide more information on how the number of periods is to be determined.
>>>
>>> Regards,
>>> Joe Canner
>>> Johns Hopkins University School of Medicine
>>>
>>> ________________________________________
>>> From: owner-statalist@hsphsun2.harvard.edu [owner-statalist@hsphsun2.harvard.edu] on behalf of Erik Aadland [erikaadland@hotmail.com]
>>> Sent: Friday, October 04, 2013 4:18 AM
>>> To: statalist@hsphsun2.harvard.edu
>>> Subject: st: Merge problem
>>>
>>> Dear Statalist.
>>>
>>> I have two datasets. One dataset (A) contains the variables "year" and "firm_id". A firm observation ("firm_id") occurs only once in a given "year".
>>>
>>> The other dataset (B) contains the variables "year", "period_id" and "firm_id". There are many periods within a given year, and firm observations ("firm_id") are nested within periods. So a given firm ("firm_id") may occur several times in a given year. The firms in (A) are not the same firms as in (B). The structure of (A) and (B) are as follows.
>>>
>>> (A):
>>> year firm_id
>>> 2003 1
>>> 2003 2
>>> 2003 3
>>> 2003 4
>>> 2004 1
>>> 2004 2
>>> 2004 5
>>> 2004 6
>>>
>>> (B):
>>> year period_id firm_id
>>> 2003 1 11
>>> 2003 1 12
>>> 2003 2 13
>>> 2003 2 14
>>> 2003 2 11
>>> 2004 3 11
>>> 2004 3 12
>>> 2004 3 15
>>> 2004 4 16
>>> 2004 4 17
>>>
>>> I want to merge the firms in (A) into (B) such that the firms in (A) in a given year occur in all periods for the corresponding year in (B). The problem is that I don't have "period_id" for my firm observations in (A).
>>>
>>> Is there a smart way to handle this problem? I use Stata 12.
>>>
>>> Any input on this would be much appreciated.
>>>
>>> Sincerely,
>>> Erik
>>> *
>>> * 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/
>
> *
> * 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/