topic Re: What type of join? in SAS Programming

What type of join?

jenim514 — Tue, 03 Nov 2015 19:19:31 GMT

I need to join several tables (3 fiscal months...FM01, FM02, FM03) selecting specific variables that are the same in all the tables and combining all the observations within those tables.

proc sql;

create table FM123 as

select patssn, patsex,patrace,patage

from FM01, FM01, FM03 *** not sure what typle of join or where to type the join statement?!

order by patssn;

quit;

Re: What type of join?

Reeza — Tue, 03 Nov 2015 19:28:34 GMT

Do you need them side by side or stacked? I would think a union all would work if stacked.

select * from fm01
union all
select * from fm02

If side by side you need a joining variable and you'll need to rename all the variables from each dataset since you can't have multiple variable with the same name.

Re: What type of join?

jenim514 — Tue, 03 Nov 2015 19:36:05 GMT

That worked! Thank you!!

Re: What type of join?

jenim514 — Tue, 03 Nov 2015 20:00:26 GMT

Union All does work, however Is there a way to join/ merge several tables without having select all the variables from every table I am combining? Say I'm working with 30 variables (out of 150) and I only want to select/ type them once because they are going to be the same variables in every table (stacked observations). Just want to know if there is an easier way to join/ merge the tables.

Re: What type of join?

ndp — Tue, 03 Nov 2015 20:17:39 GMT

First you need to understand that joining (commonly known as merging) and stacking tables are 2 different operations.

1) If you are joining them (meaning side by side) then as previously mentioned you need to have one or more id variables to join by and you need to rename varibles that have same name in more than one dataset.

2) If you are stacking them (meaning one after other) then you do not need one or more id variables and you do not need to rename variables that have same name and are of same type.

If you do not want to type out variables again and again you can put them in a macro variable eg: %let vartokeep= var1 var2 ...;

then if you are stacking them together you can use a simple data step:

data want;

set have1(keep=&vartokeep.) have2(keep=&vartokeep) ...;

run;

Re: What type of join?

Reeza — Tue, 03 Nov 2015 20:28:25 GMT

I'm with @ndp - using a data step is easier in this case. Especially because you can use variable lists in a data step and you can't in proc sql.

data want;
set fm01 fm02 fm03;

/*some examples of keep statements*/
keep var1-var30; /*all variables numbered from var1-var30*/
keep prefix:; /*all variables that start with prefix*/
keep var1--random20; /*all variables that are between var1 and random20 if you look at the variables in a table*/

run;

Re: What type of join?

jenim514 — Tue, 03 Nov 2015 21:19:11 GMT

Thank you!!