BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
gabonzo
Quartz | Level 8

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

1 ACCEPTED SOLUTION
3 REPLIES 3
Tom
Super User Tom
Super User

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


gabonzo
Quartz | Level 8
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?

hackathon24-white-horiz.png

2025 SAS Hackathon: There is still time!

Good news: We've extended SAS Hackathon registration until Sept. 12, so you still have time to be part of our biggest event yet – our five-year anniversary!

Register Now

How to Concatenate Values

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 3 replies
  • 4314 views
  • 0 likes
  • 2 in conversation