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 |
Jeph Herrin <stata@spandrel.net> |

To |
statalist@hsphsun2.harvard.edu |

Subject |
Re: st: generate new distinct individual ids by organizational id and year |

Date |
Fri, 10 May 2013 08:58:52 -0400 |

Sorry, yes, ind_id is what I meant. On 5/10/2013 3:32 AM, Erik Aadland wrote:

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, ErikDate: 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/

**References**:**st: generate new distinct individual ids by organizational id and year***From:*Erik Aadland <erikaadland@hotmail.com>

**Re: st: generate new distinct individual ids by organizational id and year***From:*Jeph Herrin <stata@spandrel.net>

**RE: st: generate new distinct individual ids by organizational id and year***From:*Erik Aadland <erikaadland@hotmail.com>

**Re: st: generate new distinct individual ids by organizational id and year***From:*Jeph Herrin <stata@spandrel.net>

**RE: st: generate new distinct individual ids by organizational id and year***From:*Erik Aadland <erikaadland@hotmail.com>

- Prev by Date:
**st: graphing estimates and confidence intervals** - Next by Date:
**st: Mann-Whitney test using only summary data** - Previous by thread:
**RE: st: generate new distinct individual ids by organizational id and year** - Next by thread:
**st: logit average "marginal" effect of dummy variable with predict** - Index(es):