topic Re: Proc Export to Excel - Multiple data sets to multiple sheets in SAS Programming

Proc Export to Excel - Multiple data sets to multiple sheets

hwangnyc — Thu, 28 Jul 2016 19:08:32 GMT

Hi all,

Lets say I have 3 data sets:

Data1, Data2, Data3

and I want to export it to a excel file called AllData

And have 3 sheets: Data1 (containing all the information from Data1), Data2, Data3.

Is this possible?

Thanks!

Re: Proc Export to Excel - Multiple data sets to multiple sheets

PBsas — Thu, 28 Jul 2016 19:21:17 GMT

You can use proc export or ODS:

proc export data=table1 out="path/filename.xlsx" dbms=xlsx replace; sheet="sheet_name1";run;

proc export data=table2 out="path/filename.xlsx" dbms=xlsx replace; sheet="sheet_name2";run;

proc export data=table3 out="path/filename.xlsx" dbms=xlsx replace; sheet="sheet_name3";run;

Remember to have same output file name otherwise will create different files.

Re: Proc Export to Excel - Multiple data sets to multiple sheets

SASKiwi — Thu, 28 Jul 2016 19:36:54 GMT

You can also use the Excel LIBNAME engine and PROC DATASETS.

Re: Proc Export to Excel - Multiple data sets to multiple sheets

Reeza — Thu, 28 Jul 2016 21:23:09 GMT

Libname outXL XLSX 'path to excel file.xlsx';

Proc datasets library=work nodetails nolist;
Copy in=work out=outXL;
Select data1-data3;
Run;quit;

Libname outXL;

If on SAS 9.4 M3 you can also use ODS EXCEL