topic Re: SAS query in SAS Programming

SAS query

scb — Wed, 15 Nov 2017 02:59:27 GMT

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

Re: SAS query

Astounding — Wed, 15 Nov 2017 04:08:18 GMT

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.

Re: SAS query

Tom — Wed, 15 Nov 2017 04:11:58 GMT

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;

Re: SAS query

PGStats — Wed, 15 Nov 2017 06:05:03 GMT

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;

Re: SAS query

Ksharp — Wed, 15 Nov 2017 12:58:52 GMT

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;

Re: SAS query

ballardw — Wed, 15 Nov 2017 16:25:06 GMT

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.