Export multiple sas file into multiple excel worksheet of a single workbook

[abraham1]

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;

[maguiremq]

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/

[PaigeMiller]

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.

[abraham1]

Thank you so much. Its working fine. As I have more dataset, I want the whole code to convert into macro. I am not that much expert in macro. The below code is not working. Can you please help one more time.

%macro export(supp);
proc export
data=aa.&supp
dbms=xlsx
outfile="C:/output/test.xlsx"
replace;
sheet=&supp;
run;
%mend;
%export(suppds)

[PaigeMiller]

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.

[Kurt_Bremser · Accepted solution]

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.

[abraham1]

Thank you so much. Its working fine getting waring message for every dataset
'WARNING: Labels exceeding length 32 are not supported by engine XLSX and are being truncated'
Is it ok?

[Kurt_Bremser]

You can ignore the message for all practical purposes; to avoid it, create your datasets without labels or labels that fit into 32 characters.