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

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!

1 ACCEPTED SOLUTION

Accepted Solutions
Jagadishkatam
Amethyst | Level 16

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

2 REPLIES 2
Jagadishkatam
Amethyst | Level 16

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
yeaforme
Calcite | Level 5

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!

Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 2 replies
  • 18004 views
  • 3 likes
  • 2 in conversation