topic Re: Identifying a combined ID var across multiple rows in SAS Programming

Identifying a combined ID var across multiple rows

Walternate — Mon, 01 Feb 2016 18:07:58 GMT

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

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.

Re: Identifying a combined ID var across multiple rows

Astounding — Mon, 01 Feb 2016 19:26:27 GMT

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.

Re: Identifying a combined ID var across multiple rows

RW9 — Tue, 02 Feb 2016 10:29:44 GMT

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;

Re: Identifying a combined ID var across multiple rows

LinusH — Tue, 02 Feb 2016 16:14:03 GMT

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?