topic Re: Exporting multiple SAS datasets into different sheets of excel file in SAS Programming

Exporting multiple SAS datasets into different sheets of excel file

petlove — Fri, 27 Jul 2018 19:04:30 GMT

Hi,

I am trying to create one excel file with different sheets for each dataset by using proc export. Following is my macro:

%macro export;

libname output "XXXXX";

%let domains=AE#DM;

%do i = 1 %to %eval(%sysfunc(count(&domains,#))+1);
%let dsn=%scan(&domains,&i,#);

proc export data=&dsn label
dbms=xlsx
outfile="test.xlsx"
replace;
;
sheet="&dsn.out" ;
run;

libname output;

%end;

%mend;

options merror serror symbolgen mlogic;
%export;

This macro overrides the existing sheet of AE.

Please let me know how to not override the previous sheet or append these sheets.

Thank you.

Re: Exporting multiple SAS datasets into different sheets of excel file

cau83 — Fri, 27 Jul 2018 19:14:44 GMT

Use "options mprint symbolgen;" as well as some %put statements to check the values of those parameters and see what is happening in each iteration of hte loop. It looks to me like the sheet names it would write out would be AEout.

Re: Exporting multiple SAS datasets into different sheets of excel file

PeterClemmensen — Fri, 27 Jul 2018 19:15:40 GMT

You can add the SHEET= along with the REPLACE option to your PROC EXPORT step.

Re: Exporting multiple SAS datasets into different sheets of excel file

petlove — Fri, 27 Jul 2018 21:04:05 GMT

I already have SHEET= option in macro

Re: Exporting multiple SAS datasets into different sheets of excel file

Ksharp — Sat, 28 Jul 2018 11:42:18 GMT

libname x xlsx 'c:\temp\want.xlsx' ;

libname x excel 'c:\temp\want.xlsx';

proc copy in=work out=x;

select ae stdm;

run;