topic Re: Excel export to multiple sheets not working with filerefs in SAS Programming

Excel export to multiple sheets not working with filerefs

gabonzo — Fri, 10 Sep 2021 15:52:18 GMT

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

Re: Excel export to multiple sheets not working with filerefs

Tom — Fri, 10 Sep 2021 16:34:38 GMT

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

Re: Excel export to multiple sheets not working with filerefs

gabonzo — Fri, 10 Sep 2021 17:16:28 GMT

It works for me on my other PC, which has 9.4 M4 if I am not wrong. Perhaps it's a bug introduced in the newest release?

Re: Excel export to multiple sheets not working with filerefs

gabonzo — Wed, 16 Feb 2022 15:04:46 GMT

It's a known bug: https://support.sas.com/kb/68/654.html