topic Merging two SAS datasets in SAS Programming

Merging two SAS datasets

Jack1 — Tue, 12 Jul 2016 18:35:32 GMT

Hello, I have 2 SAS datasets that I need to merge: one is called revenue and the other is called expenses. Each dataset contains the same 3 variable names: corp_stat, naics and prov_code which represent corporate status, industry and region respectively. To correctly match revenues and expenses, the 3 variables corp_stat, naics and prov_code must match so revenue & expenses match with their corresponding industry/region/corporate status. How would I code this? Would SQL or a merge statement be easier? Thanks Jack

Re: Merging two SAS datasets

ballardw — Mon, 18 Jul 2016 20:38:08 GMT

The choice between Merge in a datastep and an SQL Join would usually come up when you have many to one or many to many questions. If there is only one of each then either should produce the same result.

I would probably use Proc SQL as that wouldn't require sorting the data first:

proc sql;

create table want as

select a.*, b.expenses

from revenuedataset as a left join expensedataset as b

on a.corp_stat=b.corp_stat and a.naics=b.naics and a.prov_code=b.prov_code;

quit;

Re: Merging two SAS datasets

ChrisNZ — Wed, 13 Jul 2016 04:48:11 GMT

If you are new enough to SAS to ask, I reckon a data step is better. It gives much more information about how data was processed, and its syntax is less prone to errors.

SQL has a more natural syntax, but this apparent familiarity is misleading and it is very easy to *not* get the expected result.

Re: Merging two SAS datasets

Jack1 — Wed, 13 Jul 2016 15:55:37 GMT

Thanks for the info....Jack

Re: Merging two SAS datasets

Jack1 — Wed, 13 Jul 2016 15:55:57 GMT

Thanks for the advice....Jack