Identifying a combined ID var across multiple rows

[Walternate]

Hi,

I have a dataset which is at the person-month level. It has 2 ID variables and a month variable:

Dataset1:

ID1     ID2     month

1        abc     200901

1        abc     200902

          abc     200903

1        abc     200904

          def      200901

          def      200902

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:

ID3_calc

1

abc

def

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.

[Astounding]

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);

quit;

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.

Good luck.

[RW9]

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;

[LinusH]

Perhaps I don't understand the underlying requirement, or you have selected non appropriate sample data. But, with your logic you are splitting up a homogeneous I'd in ID2 into separate id's. How can that be correct?