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
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
Sorry, the output data is the following
ID matchto kp
001 1 1
003 3 1
004 1 0
006 3 0
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
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;
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.