BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
lillymaginta
Lapis Lazuli | Level 10
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
1 ACCEPTED SOLUTION

Accepted Solutions
Ksharp
Super User
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;

View solution in original post

3 REPLIES 3
RichardDeVen
Barite | Level 11

You will need to track the MATCHTO values in some manner for later comparison. Tracking possibilities:

  • array
  • hash
  • query

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) ;

 

ErikLund_Jensen
Rhodochrosite | Level 12

Hi @lillymaginta 

 

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;
Ksharp
Super User
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;

hackathon24-white-horiz.png

2025 SAS Hackathon: There is still time!

Good news: We've extended SAS Hackathon registration until Sept. 12, so you still have time to be part of our biggest event yet – our five-year anniversary!

Register Now

How to Concatenate Values

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 3 replies
  • 1133 views
  • 5 likes
  • 4 in conversation