BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Jack1
Obsidian | Level 7
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
1 ACCEPTED SOLUTION

Accepted Solutions
ballardw
Super User

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

4 REPLIES 4
ballardw
Super User

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
Obsidian | Level 7

Thanks for the info....Jack

ChrisNZ
Tourmaline | Level 20

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
Obsidian | Level 7

Thanks for the advice....Jack

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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