Two Datasets

[twildone]

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.

[ballardw · Accepted solution]

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]

Hi Ballardw,

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