topic Re: How to left join multiple tables in one step in SAS Programming

How to left join multiple tables in one step

Re: How to left join multiple tables in one step

Re: How to left join multiple tables in one step

Re: How to left join multiple tables in one step

LisaYIN9309 — Thu, 26 Oct 2017 17:54:42 GMT

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.....

I created small tables like this:

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

Age	p
Age: < 18 years
Age:18-25 years

Age	q
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!

LisaYIN9309 — Thu, 26 Oct 2017 17:55:29 GMT

to correct, the third table should be Age 26-35 years

Astounding — Thu, 26 Oct 2017 18:22:06 GMT

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.

Age: < 18 years

Age:18-25 years

Age: 26-35 years

Age: >35 years

Age: Unknown

LisaYIN9309 — Thu, 26 Oct 2017 20:46:36 GMT

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