topic Re: Only keep matching values in SAS Enterprise Guide

Only keep matching values

jcapua2 — Wed, 08 Nov 2017 17:18:29 GMT

I have a dataset with patient_type=living and patient_type=deceased, as well as a column for match_ID.

I want to keep only observations where patient_type=living and patient_type=deceased have the same match_ID, and exclude everything else.

For example,

Key Px_Type Match_ID

1115 L 0001a

1116 D 0001a

1117 D 0004b

1118 L 0003b

I want to keep observations 1115 (L) and 1116 (D) since they have the same match_ID. Is there a way to approach this in SAS?

Re: Only keep matching values

RW — Wed, 08 Nov 2017 19:49:34 GMT

If your data is sorted then you could try a statement like this

if compress(Match_ID) eq lag(compress(Match_ID)) and compress(Px_Type) in ( "D","L") then output ;

Re: Only keep matching values

ballardw — Wed, 08 Nov 2017 20:33:41 GMT

Is there other data that has to be preserved? If so, what exactly would the result look like?

Re: Only keep matching values

Ksharp — Thu, 09 Nov 2017 13:26:06 GMT

data have;
input Key     Px_Type $  Match_ID $;
cards;
1115    L               0001a
1116    D              0001a
1117    D              0004b
1118    L               0003b
;
run;

proc sql;
select *
 from have
  group by  Match_ID
   having count(distinct  Px_Type) ne 1;
quit;