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.
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.