Merging two SAS datasets

[Jack1]

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

[ballardw · Accepted solution]

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;

[Jack1]

Thanks for the info....Jack

[ChrisNZ]

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.

[Jack1]

Thanks for the advice....Jack