DATA Step, Macro, Functions and more

Proc Export to Excel - Multiple data sets to multiple sheets

Reply
Contributor
Posts: 55

Proc Export to Excel - Multiple data sets to multiple sheets

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! 

Contributor
Posts: 27

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

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.

Super User
Posts: 3,260

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

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

Super User
Posts: 19,867

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

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 

 

Ask a Question
Discussion stats
  • 3 replies
  • 228 views
  • 0 likes
  • 4 in conversation