BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
jenim514
Pyrite | Level 9

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;

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Reeza
Super User
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.

View solution in original post

6 REPLIES 6
Reeza
Super User
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.
jenim514
Pyrite | Level 9

That  worked!  Thank you!!

jenim514
Pyrite | Level 9

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. 

ndp
Quartz | Level 8 ndp
Quartz | Level 8

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;

Reeza
Super User

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;
jenim514
Pyrite | Level 9
Thank you!!

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