CODEWITHSUNDEEP
sqlsasouter-join
Roo
Roo

Reputation: 912

Stacking multiple tables with different structure into one table using proc sql

I created multiple tables in SAS with different field names and I want to stack these tables into one table and export it to the Excel. I know this is not standard but it works for me to export a table to Excel instead of multiple ones.

How may I do that in proc sql?

enter image description here

Upvotes: 2

Views: 1382

Answers (3)

Johnathan Jacobs
Johnathan Jacobs

Reputation: 46

How about using outer union?

If I understand the question correctly, you want all the columns from all the datasets.

Here's an example:

data test1;
x=1;
y=2;
run;
data test2;
    x=2;
    y=2;
    z=1;
run;
data test3;
    x=1;
    o=14;
    p=12;
run;

proc sql;

    create table TEST_O as
        select * from test1
        OUTER UNION
        select * from test2
        OUTER UNION
        select * from test3
    ;
quit;

of course you wouldn't have the same column names across them, but you could preprocess the datasets and rename the columns dynamical, or just rename them in the select and use union/union all as below:

proc sql;

    create table TEST_O2 as
        select x as col1, y as col2 from test1
        UNION all
        select x as col1, y as col2, z as col3 from test2
        UNION all
        select  x as col1, o as col2, p as col3 from test3
    ;
quit;

Upvotes: 1

Kiran
Kiran

Reputation: 3315

@Reeza answer is a clean one but if you want to the same thing in proc sql then you need to use name of columns you want to do in the insert statement( data types should match). let me illustrate through an example

 /* first create table with most columns you want*/
 proc sql;
 create table class as
 select * from sashelp.class; 

 /*first create table with one less column*/
 proc sql;
 create table class1(drop=height) as
 select * from sashelp.class;

/*createsecond table with one less column*/
proc sql;
create table class2(drop = height sex)
as select * from class;
 /* insert can be done into first table with lesser columns by explicitly mentioning  columns in parenthesis as shown below */
 proc sql;
insert into class(name,sex, age, weight)
select * from class1;

 /* insert can be done into first table with  more lesser columns by explicitly 
   mentioning  columns in parenthesis as shown below */
 proc sql;
insert into class(name,age, weight)
select * from class2;

then you can do proc export to excel

Upvotes: 1

Reeza
Reeza

Reputation: 21294

Use ODS EXCEL instead if you're using SAS 9.4 TS1M3+.

ods excel file = 'C:\_localdata\demo.xlsx' options(sheet_interval = 'none') style=meadow;

   proc print data=sashelp.cars (obs=5);
   proc print data=sashelp.air (obs=5);
   proc print data=sashelp.class (obs=5);
   run;

ods excel close;

Upvotes: 3

Related Questions