Re: Two Datasets

Accepted Solution Solved
Reply
Regular Contributor
Posts: 221
Accepted Solution

Re: Two Datasets

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.


Accepted Solutions
Solution
‎07-22-2014 07:04 PM
Grand Advisor
Posts: 10,223

Re: Two Datasets

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


All Replies
Solution
‎07-22-2014 07:04 PM
Grand Advisor
Posts: 10,223

Re: Two Datasets

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;

Regular Contributor
Posts: 221

Re: Two Datasets


Hi Ballardw,

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

☑ This topic is SOLVED.

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

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