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: generate new distinct individual ids by organizational id and year
From
Jeph Herrin <[email protected]>
To
[email protected]
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: [email protected]
To: [email protected]
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: [email protected]
To: [email protected]
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/
*
* 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/