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;
That worked! Thank you!!
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.
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;
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;
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!
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.