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
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.
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;
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;
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;
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.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.