06-10-2017 08:11 PM - edited 06-10-2017 08:52 PM
On the Combine attachment, I have a NEW table contains three variables. Another OLD table contains only five variables without any observations. The variable format of OLD table is the same as NEW. The Age is three digit and the Sex is the character. However, the tables list in the attachment are only just examples. My actual table NEW has 400 variables and OLD table has 600 variables. How to merge them to a table containing all the variables and observations, like 'Merge' tab of the Excel sheet? Thanks.
06-10-2017 08:32 PM
say you table abc then alter table add new columns(b,c) you want.
alter table abc add b char(10), c num format=10.;
06-10-2017 08:42 PM
One table is essentially your table structure.
I'm making big assumptions because you haven't provided any details.
1. Tables are alrady in SAS
2. 'Empty' table is your desired table structure
3. Types match between tables already, ie Sex is the same type (character) in both datasets.
There are several solutions to this problem, including: a data step with datasets listed in the SET statement, PROC APPEND, or a SQL Insert.
data want; set empty /*your empty table structure*/ insert /*your table with data*/; run;
proc append base = empty data=insert; run;
There is an entire section dedicated to 'combining' datasets.
Concatenating datasets is one of the first things covered.
And the section on Common Problems is one you should review since you're likely to encounter at least one if you're trying to do this.
06-14-2017 09:44 AM
I used the proc append as you suggested. However, I found the empty dataset has been changed to new merged dataset. Is there a way to create new data without using OLD empty one? Thanks.
proc append base = empty data=insert;
06-14-2017 09:01 PM
Sorry for not expressing clearly. I would like to keep the empty table (the one with names only) intact. However, I found that the final merged table was found in the table 'empty'. Could I create a new table after the merging? I would like to keep the empty table for future use repeatedly. Thanks.
06-14-2017 10:23 PM - edited 06-14-2017 10:26 PM
I commented the code. If you understand the steps you should be able to remove the step that's unnecessary. I think it's pretty clear from the comments.
Edit: there are two solutions in my post. One will do what you want. Append is just that, it appends to a data set. Im guessing you didn't read the link I posted.
06-10-2017 08:48 PM
Your idea is great. However, my actual table NEW has 400 variables and OLD table has 600 variables.
You should know by now to include sample data as text not XLSX and to include this type of information in your question from the start.
06-10-2017 10:50 PM
Not sure why you believe what @Reeza proposes will create more work for you with a lot of variables.
Below some code which illustrates how you can do this independent of the number of variables in your source data sets.
data old; stop; set sashelp.class sashelp.classfit; run; data new; length sex $3; set sashelp.class; run; /**** option 1: works well as long as same named variables are of same type and length ****/ data want; set old(obs=0) new; run; /**** option 2: outer union corr will create output table with max length of same named variables from input tables variables must still be of same data type (num or char) ****/ proc sql; create table want2 as select * from old(obs=0) outer union corr select * from new ; quit;
06-10-2017 09:04 PM - edited 06-10-2017 09:14 PM
something like below should work, answer is very similar to what @Reeza has written
/* your intial table with your records*/
data intialtable; a=10; run; /* this is your empty table as intial table where you have records*/ proc sql; create table uyuy like abc; /* this is empty table you have*/ proc sql; create table xyxx (gg num); /* here you will get all columns by cross join*/ proc sql; create table final_tabl as select a.*, b.* from uyuy a cross join xyxx b; /* do a proc append to your final_tabl
which has all columns you need and insert data from table where you have data*/ proc append base =final_tabl data =intialtab ; quit;