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