Hi,
I need to export all supplmentary SAS file (suppae, suppdm, suppds) from Supp Library into multiple sheet of a single excel workbook. I use below method and it is working fine for one dataset. Can you please help me how to proceed.
libname aa 'C:/supp'; proc export data=aa.suppae dbms=xlsx outfile="C:/output/test.xlsx" replace; run;
Use a LIBNAME, and PROC COPY:
libname aa 'C:/supp';
libname xlout xlsx "C:/output/test.xlsx";
proc copy in=aa out=xlout;
select
suppae
suppdm
suppds
;
run;
libname xlout clear;
If you want all datasets in the library to be copied, omit the SELECT statement.
Do you have access to the XLSX engine? It makes it easy to do what you want, if I'm understanding your question correctly.
https://blogs.sas.com/content/sasdummy/2015/05/20/using-libname-xlsx-to-read-and-write-excel-files/
If your problem is just those three SAS data sets, then use this:
proc export
data=aa.suppae
dbms=xlsx
outfile="C:/output/test.xlsx"
replace;
sheet='Suppae';
run;
and repeat three times, changing the file and sheet name as appropriate. Remove the "replace" option on the 2nd and 3rd repeat.
No need to jump into the complexities of writing a macro.
As in many similar cases, people want to use macros when they are unnecessary. @Kurt_Bremser shows how to do this without macros.
Use a LIBNAME, and PROC COPY:
libname aa 'C:/supp';
libname xlout xlsx "C:/output/test.xlsx";
proc copy in=aa out=xlout;
select
suppae
suppdm
suppds
;
run;
libname xlout clear;
If you want all datasets in the library to be copied, omit the SELECT statement.
You can ignore the message for all practical purposes; to avoid it, create your datasets without labels or labels that fit into 32 characters.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.