Desktop productivity for business analysts and programmers

Only keep matching values

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 11
Accepted Solution

Only keep matching values

[ Edited ]

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?


Accepted Solutions
Solution
‎12-05-2017 01:41 PM
Super User
Posts: 10,849

Re: Only keep matching values

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;

View solution in original post


All Replies
SAS Employee RW
SAS Employee
Posts: 3

Re: Only keep matching values

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 ;

Super User
Posts: 13,926

Re: Only keep matching values

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

Solution
‎12-05-2017 01:41 PM
Super User
Posts: 10,849

Re: Only keep matching values

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;
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 181 views
  • 3 likes
  • 4 in conversation