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;
SAS Innovate 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!
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.
Ready to level-up your skills? Choose your own adventure.