topic Re: Two Datasets in SAS Programming

Re: Two Datasets

twildone — Tue, 22 Jul 2014 21:49:29 GMT

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.

Re: Two Datasets

ballardw — Tue, 22 Jul 2014 23:04:22 GMT

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;

Re: Two Datasets

twildone — Tue, 22 Jul 2014 23:44:12 GMT

Hi Ballardw,

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