BookmarkSubscribeRSS Feed
ybz12003
Rhodochrosite | Level 12

Hello:

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.

11 REPLIES 11
kiranv_
Rhodochrosite | Level 12

say you table abc then alter table add new columns(b,c) you want.

 

data abc;
a=10;
run;

proc sql;
alter table abc add b char(10), c num format=10.;

quit;

Reeza
Super User

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;

Also, RTM. 

There is an entire section dedicated to 'combining' datasets. 

Concatenating datasets is one of the first things covered.

http://support.sas.com/documentation/cdl/en/lrcon/69852/HTML/default/viewer.htm#p0sz8gq6nvzcojn13pcq...

 

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.

http://support.sas.com/documentation/cdl/en/lrcon/69852/HTML/default/viewer.htm#p15jvywi5avt3cn1bee8...

 

ybz12003
Rhodochrosite | Level 12

Hi, Reeza:

 

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;
run;

Reeza
Super User

I'm not sure what you mean here. If the base data doesn't exist for PROC APPEND, SAS will create it and not produce an error. 

ybz12003
Rhodochrosite | Level 12

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.

Reeza
Super User

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. 😞

ybz12003
Rhodochrosite | Level 12

Your idea is great.  However, my actual table NEW has 400 variables and OLD table has 600 variables.

Reeza
Super User

@ybz12003 wrote:

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. 

Patrick
Opal | Level 21

@ybz12003

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;
ybz12003
Rhodochrosite | Level 12

Thanks for all your kind help.  I am still working on input the stardard sheet into the SAS.  I will try this program later. 

kiranv_
Rhodochrosite | Level 12

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;

 

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
  • 11 replies
  • 3021 views
  • 3 likes
  • 4 in conversation