RE: st: generate new distinct individual ids by organizational id and year

[Erik Aadland <erikaadland@hotmail.com>]

Dear Jeph and Statalist.

Thank you so much.
I replaced "max_id" with "ind_id" in the third line of the code to get the max "ind_id" as "max_id" is not a variable name in my dataset:

sum missing_employees, meanonly
local maxmiss=r(max) // get the max # missing
sum ind_id, meanonly
local maxid = r(max) // get the max id in the dataset
local order=ceil(log10(`maxid'+`maxmiss'))+1 // next power of 10, add 1

The code now appears to work wonderfully on my test dataset.
I will do some more testing, but this seems right.

Thanks again.

Sincerely,

Erik.





> Date: Wed, 8 May 2013 11:36:16 -0400
> From: stata@spandrel.net
> To: statalist@hsphsun2.harvard.edu
> Subject: Re: st: generate new distinct individual ids by organizational id and year
> 
> 
> Sorry, I didn't observe that ind_ids were unique across the sample (though it was true in your example); so we'll need 
> to incorporate the org_id into the new_id.
> 
> I also used -expand- incorrectly (hence the error for missing_employees=1). Try this:
> 
> sum missing_employees, meanonly
> local maxmiss=r(max) // get the max # missing
> sum max_id, meanonly
> local maxid = r(max) // get the max id in the dataset
> local order=ceil(log10(`maxid'+`maxmiss'))+1 // next power of 10, add 1
> 
> egen tagged = tag(org_id year)	 // tag one record for expanding
> expand missing_employees+1 if tagged, gen(isnew) // create new records for missing (note +1!)
> clonevar new_ind_id = ind_id
> bys org_id year isnew : replace new_ind_id = org_id*`order'+`maxid'+_n if isnew
> 
> 
> hth,
> Jeph
> 
> 
> On 5/8/2013 3:22 AM, Erik Aadland wrote:
> > Dear Jeph and Statalist.
> >
> > Thank you for your suggested code. This is on the right track, but I still have some problems.
> >
> > I tweaked the code at little bit, as max(ind_id) is for the total dataset and not by org_id:
> >
> > egen last_id = max(ind_id) // get the max id in the dataset
> > egen tagged = tag(org_id year)	 // tag for expanding
> > expand missing_employees if tagged, gen(isnew) // create new records for missing
> > clonevar new_ind_id = ind_id
> > bys org_id year isnew : replace new_ind_id = last_id+_n if isnew
> >
> > I run this code with the following small example dataset:
> >
> > year	org_id	ind_id	missing_employees
> > 2000	1	1	1
> > 2000	1	2	1
> > 2000	2	3	2
> > 2000	2	3	2
> > 2000	2	4	2
> > 2000	3	5	0
> > 2001	1	2	2
> > 2001	1	7	2
> > 2002	1	2	1
> >
> > The desired result (in terms of new_ind_id) should be something like:
> >
> > year	org_id	ind_id	missing_employees new_ind_id
> > 2000	1	1	1	 1
> > 2000	1	2	1	 2
> > 2000 1 8
> > 2000	2	3	2	 3
> > 2000	2	3	2	 3
> > 2000	2	4	2	 3
> > 2000	2	 9
> > 2000	2	 10
> > 2000	3	5	0	 5
> > 2001	1	2	2	 2
> > 2001	1	7	2	 2
> > 2001	1	 8
> > 2001	1	 11
> > 2002	1	2	1	 8
> >
> > When running the code I get the following result:
> >
> > year	org_id	 ind_id	 missing_employees	last_id	tagged	isnew	new_ind_id
> > 2000	1	 1	 1	 7	1	0	1
> > 2000	1	 2	 1	 7	0	0	2
> > 2000	2	 3	 2	 7	1	1	8
> > 2000	2	 3	 2	 7	0	0	3
> > 2000	2	 3	 2	 7	1	0	3
> > 2000	2	 4	 2	 7	0	0	4
> > 2000	3	 5	 0	 7	1	0	5
> > 2001	1	 2	 2	 7	1	1	8
> > 2001	1	 2	 2	 7	1	0	2
> > 2001	1	 7	 2	 7	0	0	7
> > 2002	1	 2	 1	 7	1	0	2
> >
> >
> > One problem seems to be that the code does not appear to recognize missing_employees == 1.
> > Another problem seems to be that the codes uses the same distinct new_ind_ind==8 for both org_id=1 and org_id ==2.
> >
> > Any input on this problem would be greatly appreciated.
> >
> > Sincerely,
> >
> > Erik
> >
> >
> >
> >
> >
> >
> >> Date: Tue, 7 May 2013 09:39:13 -0400
> >> From: stata@spandrel.net
> >> To: statalist@hsphsun2.harvard.edu
> >> Subject: Re: st: generate new distinct individual ids by organizational id and year
> >>
> >> If I understand correctly,
> >>
> >> bys org_id : egen last_id = max(ind_id) // get the max id for each org
> >> egen tagged = tag(org_id year)	 // tag for expanding
> >> expand missing_employees if tagged, gen(isnew) // create new records for missing
> >> clonevar new_ind_id = ind_id
> >> bys org_id year isnew : replace new_ind_id = last_id+_n if isnew
> >>
> >> hth,
> >> J
> >>
> >>
> >>
> >>
> >>
> >> On 5/7/2013 4:18 AM, Erik Aadland wrote:
> >>> Dear Statalist.
> >>>
> >>> I have an unbalanced panel dataset containing organizations with distinct org_id, and employees within these organizations with distinct ind_id. Some employees are missing. I have calculated the total number of missing employees per org_id per year, called "missing_employees".
> >>>
> >>> Now, by each org_id and year I need to generate a set of distinct ind_ids corresponding to the number of missing employees. The new_ind_ids generated for a distinct org_id and year should preferably be reused in later years.
> >>>
> >>> In laymans terms, I need to generate new ind_id observations for each org_id and year so that I have an ind_id for all employees in each org_id and year.
> >>>
> >>> Example:
> >>>
> >>> year org_id ind_id missing_employees
> >>> 2000 1 1 1
> >>> 2000 1 2 1
> >>> 2000 2 3 2
> >>> 2000 2 3 2
> >>> 2000 2 4 2
> >>> 2000 3 5 0
> >>> 2001 1 2 2
> >>> 2001 1 7 2
> >>>
> >>> Given such a structure, I need to generate distinct new_ind_ids e.g. starting after the last distinct ind_id I have in my dataset (e.g. if last ind_id==100, new_ind_id starts at 101):
> >>>
> >>> year org_id ind_id missing_employees new_ind_ids
> >>> 2000 1 1 1
> >>> 2000 1 2 1
> >>> 2000 1 101
> >>> 2000 2 3 2
> >>> 2000 2 3 2
> >>> 2000 2 4 2
> >>> 2000 2 102
> >>> 2000 2 103
> >>> 2000 3 5 0
> >>> 2001 1 2 2
> >>> 2001 1 7 2
> >>> 2001 1 101
> >>> 2001 1 104
> >>>
> >>> Note that for org_id==1 in 2000, I generate 1 new_ind_id == 101 since one employee was missing. In 2001 I have 2 employees missing for org_id == 1. Since I have already generated new_ind_id == 101 for org_id==1, this can be used again in 2001 and only one new_ind_id needs to be generated; new_ind_id == 104.
> >>>
> >>> If the number of missing employees for a distinct org_id goes down from one year to the next, I would like to drop the corresponding number of new_ind_ids for that year (i.e. remove some of the reused new_ind_ids).
> >>>
> >>> Any input on these problems would be greatly appreciated.
> >>>
> >>> Kind regards,
> >>>
> >>> 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/