topic Re: combine three excels in datastep and proc sql in SAS Programming

combine three excels in datastep and proc sql

BrahmanandaRao — Mon, 03 Jul 2023 04:01:10 GMT

%macro import(file,dname);	
   Proc import datafile="&file" out=&dname
   Dbms=xlsx replace;
   Getnames=yes;
   Run;
%mend;
%import(/home/u35263349/My_Folder/sheet1.xlsx,ds1);
%import(/home/u35263349/My_Folder/sheet2.xlsx,ds2);
%import(/home/u35263349/My_Folder/sheet3.xlsx,ds3);

proc sql;
create table transaction as
select * from ds1
union all
select * from ds2
union all
select * from ds3;
quit;

data master;
set ds1 ds2 ds3;
run;

Hi Guys i want to import these excel files dynamically and combine three datasets i tried data step and proc sql please suggest any alternative method using proc sql

Re: combine three excels in datastep and proc sql

Kurt_Bremser — Mon, 03 Jul 2023 05:02:27 GMT

To "stack" datasets in SQL, one has to use the UNION clause, which you already have done.

Re: combine three excels in datastep and proc sql

Patrick — Mon, 03 Jul 2023 11:04:21 GMT

Your SQL code works with your sample Excels. What's the problem/your question.

In case in your real data the columns in the Excels are not always in the same order: Use union all CORR