topic Re: keeping observation based on two variables in SAS Programming

keeping observation based on two variables

lillymaginta1 — Tue, 24 Nov 2020 20:10:05 GMT

data have;
infile cards ;
input ID    matchto  kp ;	 
cards;
001 1 1 
002 2 0
003 3 1 
004 1 0
005 2 0
006 3 0
;

I have the data above. I am trying to create a subset based on the following two conditions: a) either kp=1 then keep or b) if kp=0 but the matchto variable equal to another one where kp=1 then also keep. For example, keep id 004 and 006 since the value of "matchto" equal to the one where kp=1.

The output data

001 1 1 003 3 1 004 1 0 006 3 0

Re: keeping observation based on two variables

lillymaginta1 — Tue, 24 Nov 2020 20:11:10 GMT

Sorry, the output data is the following

ID    matchto  kp 	 
001 1 1 
003 3 1 
004 1 0
006 3 0

Re: keeping observation based on two variables

Reeza — Tue, 24 Nov 2020 20:16:34 GMT

SQL is great here.

proc sql;
select * from have
where kp=1 or matchto in (select distinct matchto from have where kp=1);
quit

@lillymaginta1 wrote:
data have;
infile cards ;
input ID    matchto  kp ;	 
cards;
001 1 1 
002 2 0
003 3 1 
004 1 0
005 2 0
006 3 0
;
I have the data above. I am trying to create a subset based on the following two conditions: a) either kp=1 then keep or b) if kp=0 but the matchto variable equal to another one where kp=1 then also keep. For example, keep id 004 and 006 since the value of "matchto" equal to the one where kp=1.

The output data

001 1 1 003 3 1 004 1 0 006 3 0

Re: keeping observation based on two variables

novinosrin — Tue, 24 Nov 2020 20:46:33 GMT

Elegant and super fast solution @Reeza

Just adding variety to menu-


data have;
infile cards ;
input ID    matchto  kp ;	 
cards;
001 1 1 
002 2 0
003 3 1 
004 1 0
005 2 0
006 3 0
;

data want;
 if _n_=1 then do;
  dcl hash h(dataset:'have(where=(kp))');
  h.definekey('matchto');
  h.definedone();
 end;
 set have;
 if kp or h.check()=0;
run;

Re: keeping observation based on two variables

lillymaginta1 — Tue, 24 Nov 2020 21:47:40 GMT

Thank you both!