Hi,
can anyone please explain to me why this code works fine, and I get an Excel workbook with four sheets:
proc export dbms=xlsx outfile="S:\Employee\XXXXXXX\LTC_43_turnout.xlsx" data=prov_sum replace;
sheet="by Province";
run;
proc export dbms=xlsx outfile="S:\Employee\XXXXXXX\LTC_43_turnout.xlsx" data=fed_sum(where=(site_typ_id=18)) replace;
sheet="by FED - LTC";
run;
proc export dbms=xlsx outfile="S:\Employee\XXXXXXX\LTC_43_turnout.xlsx" data=fed_sum(where=(site_typ_id=15)) replace;
sheet="by FED - Seniors' Home";
run;
proc export dbms=xlsx outfile="S:\Employee\XXXXXXX\LTC_43_turnout.xlsx" data=fed_sum(where=(site_typ_id=9)) replace;
sheet="by FED - Hospital";
run;
While the exact same code, only using a file reference instead of a hardcoded filename, produces a workbook with the last sheet only?
filename outF "S:\Employee\XXXXXXX\LTC_43_turnout.xlsx";
proc export dbms=xlsx outfile=outF data=prov_sum replace;
sheet="by Province";
run;
proc export dbms=xlsx outfile=outF data=fed_sum(where=(site_typ_id=18)) replace;
sheet="by FED - LTC";
run;
proc export dbms=xlsx outfile=outF data=fed_sum(where=(site_typ_id=15)) replace;
sheet="by FED - Seniors' Home";
run;
proc export dbms=xlsx outfile=outF data=fed_sum(where=(site_typ_id=9)) replace;
sheet="by FED - Hospital";
run;
Is there a magic, secret part of the SAS documentation I might have overlooked? My SAS version is 9.4M7
It's a known bug: https://support.sas.com/kb/68/654.html
Works fine for me on Windows SAS version 9.4 (TS1M5)
%let path=%sysfunc(pathname(work));
%let fname=&path/test_file.xlsx;
filename xlsx "&fname";
proc export dbms=xlsx data=sashelp.class outfile=xlsx replace ;
sheet="sheet1";
run;
proc export dbms=xlsx data=sashelp.class outfile=xlsx replace ;
sheet="sheet2";
run;
libname check xlsx "&fname";
proc copy inlib=check outlib=work;
run;
Log
13 %let path=%sysfunc(pathname(work)); 14 %let fname=&path/test_file.xlsx; 15 16 filename xlsx "&fname"; 17 proc export dbms=xlsx data=sashelp.class outfile=xlsx replace ; 18 sheet="sheet1"; 19 run; NOTE: The export data set has 19 observations and 5 variables. NOTE: "XLSX" file was successfully created. NOTE: PROCEDURE EXPORT used (Total process time): real time 0.02 seconds cpu time 0.03 seconds 20 21 proc export dbms=xlsx data=sashelp.class outfile=xlsx replace ; 22 sheet="sheet2"; 23 run; NOTE: The export data set has 19 observations and 5 variables. NOTE: "XLSX" file was successfully created. NOTE: PROCEDURE EXPORT used (Total process time): real time 0.03 seconds cpu time 0.01 seconds 24 25 libname check xlsx "&fname"; NOTE: Libref CHECK was successfully assigned as follows: Engine: XLSX Physical Name: C:...\test_file.xlsx 26 proc copy inlib=check outlib=work; 27 run; NOTE: Copying CHECK.SHEET1 to WORK.SHEET1 (memtype=DATA). NOTE: BUFSIZE is not cloned when copying across different engines. System Option for BUFSIZE was used. NOTE: The import data set has 19 observations and 5 variables. NOTE: There were 19 observations read from the data set CHECK.SHEET1. NOTE: The data set WORK.SHEET1 has 19 observations and 5 variables. NOTE: Copying CHECK.SHEET2 to WORK.SHEET2 (memtype=DATA). NOTE: BUFSIZE is not cloned when copying across different engines. System Option for BUFSIZE was used. NOTE: The import data set has 19 observations and 5 variables. NOTE: There were 19 observations read from the data set CHECK.SHEET2. NOTE: The data set WORK.SHEET2 has 19 observations and 5 variables. NOTE: PROCEDURE COPY used (Total process time): real time 0.02 seconds cpu time 0.03 seconds
It's a known bug: https://support.sas.com/kb/68/654.html
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.