BookmarkSubscribeRSS Feed
Obsidian | Level 7



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

          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:






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.



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.


Good luck.

Diamond | Level 26 RW9
Diamond | Level 26

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;
Tourmaline | Level 20
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?
Data never sleeps

Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 3 replies
  • 4 in conversation