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

Hi.....I have two datasets of which one dataset is a sub dataset of the second dataset and both datasets have the same number of variables and the variables have exactly the same variable names. I would like to remove or exclude those records that are listed in the smaller subset from those records listed in the larger second dataset. I was wondering if a Proc Sql  with a left join or a Data Step with a Merge of the two datasets be the easier and most efficient way to achieve the end results. Thanks in advance.

1 ACCEPTED SOLUTION

Accepted Solutions
ballardw
Super User

Actually you want SQL and Except clause.

proc sql;

  create NewTable as

  select * from sql.a

  except

  select * from sql.b;

quit;

is a general form of syntax to get a new table that is the first, sql.a for example, minus records in the second set, sql.b here.

This comparison looks at ALL values of ALL variables to exclude matches in the second set. If you have some some variables with different values for an otherwise similar record it won't get removed. In which case the approach is to identify the variables that uniquely identify each record, use them in the above step for the new table and then use a left join with the NewTable and the first data set. The left join can use the above syntax as a subquery as below where Idvar1 and 2 are sufficient to uniquely identify each record.

proc sql;

     create NewTable as

     select b.*

     from (

  select idvar1, idvar2 from sql.a

  except

  select idvar1, idvar2  from sql.b) as keep

     left join sql.a as b on keep.idvar1=b.idvar1 and keep.idvar2=b.idvar2;

quit;

View solution in original post

2 REPLIES 2
ballardw
Super User

Actually you want SQL and Except clause.

proc sql;

  create NewTable as

  select * from sql.a

  except

  select * from sql.b;

quit;

is a general form of syntax to get a new table that is the first, sql.a for example, minus records in the second set, sql.b here.

This comparison looks at ALL values of ALL variables to exclude matches in the second set. If you have some some variables with different values for an otherwise similar record it won't get removed. In which case the approach is to identify the variables that uniquely identify each record, use them in the above step for the new table and then use a left join with the NewTable and the first data set. The left join can use the above syntax as a subquery as below where Idvar1 and 2 are sufficient to uniquely identify each record.

proc sql;

     create NewTable as

     select b.*

     from (

  select idvar1, idvar2 from sql.a

  except

  select idvar1, idvar2  from sql.b) as keep

     left join sql.a as b on keep.idvar1=b.idvar1 and keep.idvar2=b.idvar2;

quit;

twildone
Pyrite | Level 9


Hi Ballardw,

Thanks for you quick response and that worked perfectly and what I was hoping the end result would be.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

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.

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
  • 462 views
  • 0 likes
  • 2 in conversation