topic Re: How to merge one table with another table containing no observations? in SAS Programming

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

ybz12003 — Sun, 11 Jun 2017 00:52:55 GMT

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.

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

kiranv_ — Sun, 11 Jun 2017 00:32:37 GMT

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;

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

Reeza — Sun, 11 Jun 2017 00:42:53 GMT

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#p0sz8gq6nvzcojn13pcqe3twvg1d.htm

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#p15jvywi5avt3cn1bee8r6c33ux1.htm

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

ybz12003 — Sun, 11 Jun 2017 00:43:11 GMT

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

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

Reeza — Sun, 11 Jun 2017 00:48:01 GMT

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

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

kiranv_ — Sun, 11 Jun 2017 01:14:11 GMT

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;

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

Patrick — Sun, 11 Jun 2017 02:50:19 GMT

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

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

ybz12003 — Mon, 12 Jun 2017 19:30:10 GMT

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

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

ybz12003 — Wed, 14 Jun 2017 13:44:09 GMT

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;

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

Reeza — Wed, 14 Jun 2017 22:18:15 GMT

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.

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

ybz12003 — Thu, 15 Jun 2017 01:01:17 GMT

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.

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

Reeza — Thu, 15 Jun 2017 02:26:46 GMT

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