DATA Step, Macro, Functions and more

Merging two SAS datasets

Accepted Solution Solved
Reply
Contributor
Posts: 54
Accepted Solution

Merging two SAS datasets

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

Accepted Solutions
Solution
‎07-18-2016 12:40 PM
Super User
Posts: 11,343

Re: Merging two SAS datasets

[ Edited ]

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;

View solution in original post


All Replies
Solution
‎07-18-2016 12:40 PM
Super User
Posts: 11,343

Re: Merging two SAS datasets

[ Edited ]

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;

Contributor
Posts: 54

Re: Merging two SAS datasets

Thanks for the info....Jack

PROC Star
Posts: 1,760

Re: Merging two SAS datasets

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.

Contributor
Posts: 54

Re: Merging two SAS datasets

Thanks for the advice....Jack

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 332 views
  • 0 likes
  • 3 in conversation