SAS query

[scb]

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

[Astounding]

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.  

[Tom]

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;

[PGStats]

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;

[Ksharp]

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;

[ballardw]

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.