DATA Step, Macro, Functions and more

How to merge one table with another table containing no observations?

Reply
Super Contributor
Posts: 297

How to merge one table with another table containing no observations?

[ Edited ]

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.

PROC Star
Posts: 261

Re: How to merge one table with another table containing no observations?

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;

Super User
Posts: 17,942

Re: How to merge one table with another table containing no observations?

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

 

Super Contributor
Posts: 297

Re: How to merge one table with another table containing no observations?

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;

Super User
Posts: 17,942

Re: How to merge one table with another table containing no observations?

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. 

Super Contributor
Posts: 297

Re: How to merge one table with another table containing no observations?

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.

Super User
Posts: 17,942

Re: How to merge one table with another table containing no observations?

[ Edited ]

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. Smiley Sad

Super Contributor
Posts: 297

Re: How to merge one table with another table containing no observations?

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

Super User
Posts: 17,942

Re: How to merge one table with another table containing no observations?


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. 

Respected Advisor
Posts: 3,902

Re: How to merge one table with another table containing no observations?

@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;
Super Contributor
Posts: 297

Re: How to merge one table with another table containing no observations?

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

PROC Star
Posts: 261

Re: How to merge one table with another table containing no observations?

[ Edited ]

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;

 

Ask a Question
Discussion stats
  • 11 replies
  • 227 views
  • 3 likes
  • 4 in conversation