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.
Actually you want SQL and Except clause.
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;
Actually you want SQL and Except clause.
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;
Hi Ballardw,
Thanks for you quick response and that worked perfectly and what I was hoping the end result would be.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.