11-03-2015 02:19 PM
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.
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;
11-03-2015 02:28 PM
11-03-2015 02:28 PM
11-03-2015 03:00 PM
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.
11-03-2015 03:17 PM
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:
set have1(keep=&vartokeep.) have2(keep=&vartokeep) ...;
11-03-2015 03:27 PM - edited 11-03-2015 03:28 PM
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;
Need further help from the community? Please ask a new question.