02-01-2016 01:07 PM
I have a dataset which is at the person-month level. It has 2 ID variables and a month variable:
ID1 ID2 month
1 abc 200901
1 abc 200902
1 abc 200904
I created a third ID variable which uses ID1 as the base ID (it doesn't look like it from the dataset above, but ID1 is more likely to be populated and cleaner than ID2). However, each row that does not have an ID1 will have the value of ID2 for the new ID variable:
ID1 ID2 month ID3_calc
1 abc 200901 1
1 abc 200902 1
abc 200903 abc
1 abc 200904 1
def 200901 def
def 200902 def
Ultimately I put the data at the person-level, so there is one row per person with ID3_calc:
The issue is that I want to divide into 2 datasets: Dataset 2 would have all people for which ID3_calc=ID1. Dataset 3 would have all people for whom ID3_calc=ID2, but only those in which the ID2 does not appear anywhere in Dataset 1. That is, for Dataset 3, I would only want ID3_calc=def, because that person does not have any rows in Dataset 1 in which they do have a value of ID1, while ID3_calc=abc does have rows in Dataset1 under ID1=1.
The people like ID3_calc=abc, for whom I have data under both their ID1 and ID2, I would want to just keep the variables based around their ID1s and delete the rows grouped by their ID2s.
Any help is much appreciated.
02-01-2016 02:26 PM
Well, my SQL isn't the strongest so you may need to debug this a bit. But it does seem like a task that SQL would handle well.
proc sql noprint;
create table id1_ungrouped as select * from have where (id1 > ' ');
create table id2_ungrouped as select * from have where id1=' ' and id2 not in (select distinct id2 from id1_ungrouped);
At this point, ID1 would be the identifier for observations in id1_ungrouped, and ID2 would be the identifier for observations in id2_ungrouped.
Then you can follow whatever grouping steps you choose, possibly assigning id3_calc along the way if you combine the two data sets.
02-02-2016 05:29 AM
Can you not just coalesce() the two varibales, i.e. the first non missing is the value:
proc sql; create table WANT as select *, coalesce(ID1,ID2) as ID3_CALC from HAVE; quit;
02-02-2016 11:14 AM