DATA Step, Macro, Functions and more

SAS query

Reply
Frequent Contributor
Frequent Contributor
Posts: 78

SAS query

I have the below dataset

 

data A;
Input ID UNID STATUS $;
cards;
110 88100 VALID
111 88100 INVALID
112 99100 VALID
113 99100 INVALID
;
run;

 

May I know how to generate the below output with the above data? Thanks.

 

ID  UNID    STATUS  INVALID_ID

110 88100 VALID    111

112 99100 VALID    113

Super User
Posts: 6,632

Re: SAS query

[ Edited ]

This assumes your data set is in sorted order by UNID:

 

data want;

set have;

by UNID;

length final_status $ 7;

retain final_status final_ID invalid_ID;

if first.UNID then do;

   final_status=' ' ;

   final_ID = .;

   invalid_ID = .;

end;

if status='VALID' then do;

   final_ID = ID;

   final_status = 'VALID';

end;

else invalid_ID = ID;

if last.UNID;

if final_status = ' ' then final_status = 'INVALID';

keep final_ID UNID final_STATUS INVALID_ID;

rename final_ID=ID final_status=STATUS;

run;

 

It's untested, so it may need to be tweaked.  

Super User
Super User
Posts: 7,933

Re: SAS query

 

Perhaps you just want to merge the dataset with itself?

data want ;
  merge a (keep=id unid status where=(status='VALID'))
        a (keep=id unid status rename=(id=INVALID_ID status=xx) where=(xx='INVALID'))
  ;
  by unid ;
  drop xx ;
run;
Esteemed Advisor
Posts: 5,482

Re: SAS query

If your data realy is as simple as in your example, i.e. a sequence of valid-invalid entries, a simple transposition might be enough:

 


proc transpose data=a out=b(drop=_name_ where=(status="VALID"));
by unid;
id status;
copy status;
var id;
run;
PG
Super User
Posts: 10,689

Re: SAS query

data A;
Input ID UNID STATUS $;
cards;
110 88100 VALID
111 88100 INVALID
112 99100 VALID
113 99100 INVALID
;
run;
data want;
 merge a a(firstobs=2 keep=unid id rename=(unid=_unid id=INVALID_ID));
 if unid=_unid;
 drop _unid;
run;
Super User
Posts: 13,321

Re: SAS query

What do you want when you have multiple Valid records for a given UNID?

What do you want when you have multiple Invalid records for a given UNID?

What if there are NO Valid records but there are Invalid record(s) for a given UNID?

What if there are NO Invalid records but there are Valid record(s) for a given UNID?

 

Really need to answer these a different approaches may resolve some cases but not others.

Ask a Question
Discussion stats
  • 5 replies
  • 248 views
  • 1 like
  • 6 in conversation