Help using Base SAS procedures

Remove observations from one dataset based upon a list in another

Accepted Solution Solved
Reply
Contributor
Posts: 35
Accepted Solution

Remove observations from one dataset based upon a list in another

I have two datasets.  Dataset A has all the raw data where each subject ID code has multiple (sometimes thousands) of observations for it.  Dataset B has a list of subject ID codes that need to be removed from Dataset A (i.e., if the ID code is in Dataset B then all observations linked to the ID code need to be removed from Dataset A).

I'm thinking it would seem like a job for proc sql, but don't know how to go about it.  Thanks!


Accepted Solutions
Solution
‎10-27-2013 10:53 AM
Trusted Advisor
Posts: 1,137

Re: Remove observations from one dataset based upon a list in another

proc sort data=dataset_A;

     by id;

run;

proc sort data=dataset_B;

     by id;

run;

data want;

     merge dataset_A(in=a) dataset_B(in=b);

     by id;

     if a and not b;

run;

by proc sql;

proc sql;

    create table want as select a.* from dataset_A as a, dataset_b as b where a.id^=b.id;

quit;

Thanks,

Jagadish

Thanks,
Jag

View solution in original post


All Replies
Solution
‎10-27-2013 10:53 AM
Trusted Advisor
Posts: 1,137

Re: Remove observations from one dataset based upon a list in another

proc sort data=dataset_A;

     by id;

run;

proc sort data=dataset_B;

     by id;

run;

data want;

     merge dataset_A(in=a) dataset_B(in=b);

     by id;

     if a and not b;

run;

by proc sql;

proc sql;

    create table want as select a.* from dataset_A as a, dataset_b as b where a.id^=b.id;

quit;

Thanks,

Jagadish

Thanks,
Jag
Contributor
Posts: 35

Re: Remove observations from one dataset based upon a list in another

Posted in reply to Jagadishkatam

The SQL code creates a cartesian product that takes forever to run before timing out after it has consumed all available memory, but the data merge code above it works well.

Thanks!

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 2 replies
  • 2234 views
  • 0 likes
  • 2 in conversation