BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
LisaYIN9309
Obsidian | Level 7

 

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!

1 ACCEPTED SOLUTION

Accepted Solutions
Astounding
PROC Star

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

3 REPLIES 3
LisaYIN9309
Obsidian | Level 7
to correct, the third table should be Age 26-35 years
Astounding
PROC Star

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.

LisaYIN9309
Obsidian | Level 7
wow, great! I like this 'think out of the box' solution! I was only thinking about sql steps

SAS Innovate 2025: Call for Content

Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!

Submit your idea!

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
  • 3 replies
  • 3654 views
  • 0 likes
  • 2 in conversation