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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.