BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
serena13lee
Quartz | Level 8

I have a two data sets that look like the following simplified: 

Dataset 1                                                                                             Dataset2

Patient  Drug      StartDate        EndDate       etc                                 Drug  etc 

A             E       2016-02-20     2016-02-21      -                                     E        - 

A             F       2016-01-21     2016-01-25      -                                     G        - 

A             G      2016-03-20     2016-03-21       -

B             E      2016-01-05     2016-01-24       -

B             G      2016-01-07     2016-01-14       -

C             H      2016-02-20     2016-02-28       - 

 

I am trying to keep only the observations where the Drug lists match:

 

Patient  Drug      StartDate        EndDate       etc                                 

A             E       2016-02-20     2016-02-21      -                                                                 

A             G      2016-03-20     2016-03-21       -

B             E      2016-01-05     2016-01-24       -

B             G      2016-01-07     2016-01-14       -

 

I've tried the following but I'm not sure how to read from the drug list in the second data set: 

data remove;
   set mydata;
   if Drug = [matching drugs from drug list] then delete;
;

Thanks for any help!

 

1 ACCEPTED SOLUTION

Accepted Solutions
novinosrin
Tourmaline | Level 20

 

proc sql;

create table remove as

select *

from one

where drug in (select drug from two);

quit

 

 

View solution in original post

3 REPLIES 3
Patrick
Opal | Level 21

Something like below (untested) code should work.

data want;
  if _n_=1 then
    do;
      dcl hash druglis(dataset:'dataset2');
      druglist.defineKey('drug');
      druglist.defineData('drug');
      druglist.defineDone();
    end;
  set dataset1;
  if druglist.check()=0 then output;
run;
novinosrin
Tourmaline | Level 20

 

proc sql;

create table remove as

select *

from one

where drug in (select drug from two);

quit

 

 

Jagadishkatam
Amethyst | Level 16

Via data step

 

proc sort data=Dataset1;
by drug;
run;

proc sort data=Dataset2(keep=drug) nodupkey;
by drug;
run;

data want;
merge Dataset1(in=a) Dataset2(in=b);
by drug;
if a and b then delete;
run;

 

Thanks,
Jag

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
  • 1178 views
  • 2 likes
  • 4 in conversation