DATA Step, Macro, Functions and more

How to left join multiple tables in one step

Accepted Solution Solved
Reply
Contributor
Posts: 36
Accepted Solution

How to left join multiple tables in one step

 

My final output should like, which o, p, q, r..... are columns, each column for a different population, so this table is a benchmark across all population, it has more than 90 columns.....

Ageopqrst
Age: < 18 years      
 Age:18-25 years      
Age: 26-35 years      
Age: >35 years      
Age: Unknown      

 

I created small tables like this:

Ageo
Age: < 18 years 
 Age:18-25 years 
Age: 26-35 years 
Age: >35 years 
Age: Unknown 

 

 

Agep
Age: < 18 years 
 Age:18-25 years 

 

 

Ageq
Age: >25 
 Age:unknown 

 

(not all small columns have all the rows for age category, so I am thinking to use left join)

 

I am wondering the best way to put them together. I can export all small tables into excel and copy paste them, but it will take forever. So I am thinking proc sql left join, but it only allows me to join two tables at once, which is also not too helpful give my 90 columns....

 

Any suggestion is appreciated!


Accepted Solutions
Solution
‎10-26-2017 04:46 PM
Super User
Posts: 6,631

Re: How to left join multiple tables in one step

Posted in reply to LisaYIN9309

This is an easy thing for SAS to accomplish, if you switch to MERGE.

 

Assuming each of your 90 data sets is already sorted by AGE:

 

data want;

merge pop1-pop90;

by age;

run;

 

If the data sets have different names, there may not be an easy short-cut for naming them all.  You might have to type all 90 data set names into the MERGE statement.

View solution in original post


All Replies
Contributor
Posts: 36

Re: How to left join multiple tables in one step

Posted in reply to LisaYIN9309
to correct, the third table should be Age 26-35 years
Solution
‎10-26-2017 04:46 PM
Super User
Posts: 6,631

Re: How to left join multiple tables in one step

Posted in reply to LisaYIN9309

This is an easy thing for SAS to accomplish, if you switch to MERGE.

 

Assuming each of your 90 data sets is already sorted by AGE:

 

data want;

merge pop1-pop90;

by age;

run;

 

If the data sets have different names, there may not be an easy short-cut for naming them all.  You might have to type all 90 data set names into the MERGE statement.

Contributor
Posts: 36

Re: How to left join multiple tables in one step

Posted in reply to Astounding
wow, great! I like this 'think out of the box' solution! I was only thinking about sql steps
☑ This topic is solved.

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

Discussion stats
  • 3 replies
  • 705 views
  • 0 likes
  • 2 in conversation