DATA HAVE;
INPUT patientid drug class matchto;
datalines;
1 1 a 4
2 1 a 5
3 1 b 6
4 2 . 1
5 2 . 2
6 2 . 3
;
run;
I am trying to create a data subset where drug=1 and class =a but I want to keep the matching observations from drug=2
output dataset
1 1 a 4
2 1 a 5
4 2 . 1
5 2 . 2
DATA HAVE; INPUT patientid drug class $ matchto; datalines; 1 1 a 4 2 1 a 5 3 1 b 6 4 2 . 1 5 2 . 2 6 2 . 3 ; run; data temp; set have; by drug; if first.drug then n=0; n+1; run; proc sql; create table want as select * from temp where n in (select distinct n from temp where drug=1 and class='a'); quit;
You will need to track the MATCHTO values in some manner for later comparison. Tracking possibilities:
Examples (edited to test drug=2 before checking matchto):
DATA HAVE; INPUT patientid drug class $ matchto; datalines; 1 1 a 4 2 1 a 5 3 1 b 6 4 2 . 1 5 2 . 2 6 2 . 3 ;
* array. only works if all matchtos come after drug=1 class='a'; data want; array match[100] _temporary_; set have; if drug=1 and class='a' then do; top+1; match[top] = matchto; /* tracking */ output; /* output original */ end;
if drug=2 then do; do ix = 1 to top while (match[ix] ne patientid); end; if match[ix] eq patientid then output; /* output match */
end;
drop ix top; run;
* hash. load lookups from same as SET. does not matter where matches occur; data want; if _n_ = 1 then do; declare hash lookup(dataset:'have(where=(drug=1 and class="a"))'); lookup.defineKey('matchto'); lookup.defineData('patientid'); lookup.defineDone(); /* load tracking */ end; set have; if (drug=1 and class='a') or (drug=2 and lookup.check(key:patientid) eq 0);
/* implicit output, either original or match */ run; * existential query for selecting matched rows;
proc sql;
create table want as
select * from have as outer
where drug=1 and class='a' /* select original */
or exists ( select * from have as inner /* select a match via existential correlated sub-query */
where inner.drug=1 and inner.class='a'
and inner.matchto = outer.patientid
and inner.drug = 2
)
;
* another version using SQL; proc sql; create table want as select A.* from have A where A.drug=1 and A.class='a' union select B.* from have B where exists(select * from have where drug=1 and class='a' and matchto=B.patientid and B.drug=2) ;
First select the wanted observations (drug=1 and class =a).
Then select observations where matchto matches observations from the first select.
Finally append:
DATA HAVE;
INPUT patientid drug class$ matchto;
datalines;
1 1 a 4
2 1 a 5
3 1 b 6
4 2 . 1
5 2 . 2
6 2 . 3
;
run;
proc sql;
create table a as select * from have
where drug = 1 and class = 'a';
quit;
proc sql;
create table b as select * from have
where matchto in (select patientid from a);
quit;
data want; set a b;
run;
DATA HAVE; INPUT patientid drug class $ matchto; datalines; 1 1 a 4 2 1 a 5 3 1 b 6 4 2 . 1 5 2 . 2 6 2 . 3 ; run; data temp; set have; by drug; if first.drug then n=0; n+1; run; proc sql; create table want as select * from temp where n in (select distinct n from temp where drug=1 and class='a'); quit;
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.