topic Re: export 2 table into sheet1 and 2 tables into sheet 2 in one XLSX file in SAS Programming

export 2 table into sheet1 and 2 tables into sheet 2 in one XLSX file

Ronein — Mon, 07 Apr 2025 11:58:22 GMT

Hello

I wan to export 2 table into sheet1 and 2 tables into sheet 2

What is the way please?

/****I would like to have several tables on Sheet 1 and several more on Sheet 2***/
/****I would like to have several tables on Sheet 1 and several more on Sheet 2***/
/****I would like to have several tables on Sheet 1 and several more on Sheet 2***/
ods excel file="/usr/local/SAS/SASUsers/LabRet/UserDir/udclk79/ABC12.xlsx"
options(sheet_name="Sh1" sheet_interval="none");
title 'Table1';
proc print data=sashelp.class(obs=3) noobs;
run;
title 'Table2';
proc print data=sashelp.shoes(obs=3) noobs;
run;

ods excel options(sheet_name="Sh2" sheet_interval="none");
proc print data=sashelp.cars(obs=5);
run;
title 'Table2';
proc print data=sashelp.shoes(obs=3) noobs;
run;
ods excel close;

Re: export 2 table into sheet1 and 2 tables into sheet 2 in one XLSX file

LinusH — Mon, 07 Apr 2025 13:42:33 GMT

This blog gives some examples:
https://blogs.sas.com/content/sgf/2019/04/23/ods-excel-control/

Re: export 2 table into sheet1 and 2 tables into sheet 2 in one XLSX file

Ronein — Mon, 07 Apr 2025 14:47:38 GMT

I tried none and after none or none and after porc but nothing provide desire results

Re: export 2 table into sheet1 and 2 tables into sheet 2 in one XLSX file

svh — Mon, 07 Apr 2025 15:13:39 GMT

LinusH's post had the answer--the second 'ODS excel options' statement needs sheet_interval set to "now".

ods excel file="ABC12.xlsx"
options(sheet_name="Sh1" sheet_interval="none");
title 'Table1';
proc print data=sashelp.class(obs=3) noobs;
run;
title 'Table2';
proc print data=sashelp.shoes(obs=3) noobs;
run;

ods excel options(sheet_name="Sh2" sheet_interval="now"); /*This tells SAS to start a new sheet for next PROCs*/
proc print data=sashelp.cars(obs=5);
run;
title 'Table2';
proc print data=sashelp.shoes(obs=3) noobs;
run;
ods excel close;

Re: export 2 table into sheet1 and 2 tables into sheet 2 in one XLSX file

Tom — Mon, 07 Apr 2025 15:54:18 GMT

The value you pass with SHEET_NAME= is ignored when you tell ODS EXCEL not to make a new sheet by using SHEET_INTERVAL='NONE'.

Use SHEET_INTERVAL='NOW' instead.

ods excel options(sheet_name="Sh2" sheet_interval="now");

Re: export 2 table into sheet1 and 2 tables into sheet 2 in one XLSX file

Ronein — Fri, 09 May 2025 10:34:28 GMT

using NOW options - I get an error

/*I want to export 2 table into sheet1 and 2 tables into sheet 2*/
/*I want to export 2 table into sheet1 and 2 tables into sheet 2*/
/*I want to export 2 table into sheet1 and 2 tables into sheet 2*/
ods excel file="/usr/local/SAS/SASUsers/LabRet/UserDir/udclk79/q6.xlsx"
options(sheet_name="Sh1" sheet_interval="none" embedded_titles='yes' );
title 'Table1';
proc print data=sashelp.class(obs=3) noobs;
run;
title 'Table2';
proc print data=sashelp.shoes(obs=3) noobs;
run;
ods excel options(sheet_name="Sh2" sheet_interval="now"); /*This tells SAS to start a new sheet for next PROCs*/
proc print data=sashelp.cars(obs=5);
run;
title 'Table2';
proc print data=sashelp.shoes(obs=3) noobs;
run;
ods excel close;

Error Log-

1                                                          The SAS System                               08:38 Wednesday, May 7, 2025

1          ;*';*";*/;quit;run;
2          OPTIONS PAGENO=MIN;
3          %LET _CLIENTTASKLABEL='Program 2';
4          %LET _CLIENTPROCESSFLOWNAME='Standalone Not In Project';
5          %LET _CLIENTPROJECTPATH='';
6          %LET _CLIENTPROJECTPATHHOST='';
7          %LET _CLIENTPROJECTNAME='';
8          %LET _SASPROGRAMFILE='';
9          %LET _SASPROGRAMFILEHOST='';
10         
11         ODS _ALL_ CLOSE;
12         OPTIONS DEV=SVG;
13         GOPTIONS XPIXELS=0 YPIXELS=0;
14         %macro HTML5AccessibleGraphSupported;
15             %if %_SAS_VERCOMP_FV(9,4,4, 0,0,0) >= 0 %then ACCESSIBLE_GRAPH;
16         %mend;
17         FILENAME EGHTML TEMP;
18         ODS HTML5(ID=EGHTML) FILE=EGHTML
19             OPTIONS(BITMAP_MODE='INLINE')
20             %HTML5AccessibleGraphSupported
NOTE: The ACCESSIBLE_GRAPH option is pre-production for this release.
21             ENCODING='utf-8'
22             STYLE=HTMLBlue
23             NOGTITLE
24             NOGFOOTNOTE
25             GPATH=&sasworklocation
26         ;
NOTE: Writing HTML5(EGHTML) Body file: EGHTML
27         
28         /*I want to export 2 table into sheet1 and 2 tables into sheet 2*/
29         ods excel file="/usr/local/SAS/SASUsers/LabRet/UserDir/udclk79/q6.xlsx"
30         options(sheet_name="Sh1" sheet_interval="none" embedded_titles='yes' );
WARNING: Unsupported device 'SVG' for EXCEL destination. Using default device 'PNG'.
31         title 'Table1';
32         proc print data=sashelp.class(obs=3) noobs;
33         run;

NOTE: There were 3 observations read from the data set SASHELP.CLASS.
NOTE: PROCEDURE PRINT used (Total process time):
      real time           0.02 seconds
      user cpu time       0.03 seconds
      system cpu time     0.00 seconds
      memory              2481.03k
      OS Memory           49432.00k
      Timestamp           05/09/2025 01:33:07 PM
      Step Count                        8349  Switch Count  0
      Page Faults                       0
      Page Reclaims                     113
      Page Swaps                        0
      Voluntary Context Switches        8
      Involuntary Context Switches      0
      Block Input Operations            0
      Block Output Operations           0
      

34         title 'Table2';
35         proc print data=sashelp.shoes(obs=3) noobs;
36         run;
2                                                          The SAS System                               08:38 Wednesday, May 7, 2025


NOTE: There were 3 observations read from the data set SASHELP.SHOES.
NOTE: PROCEDURE PRINT used (Total process time):
      real time           0.02 seconds
      user cpu time       0.02 seconds
      system cpu time     0.00 seconds
      memory              605.25k
      OS Memory           49688.00k
      Timestamp           05/09/2025 01:33:07 PM
      Step Count                        8350  Switch Count  0
      Page Faults                       0
      Page Reclaims                     54
      Page Swaps                        0
      Voluntary Context Switches        3
      Involuntary Context Switches      0
      Block Input Operations            0
      Block Output Operations           0
      

1                                                          The SAS System                                  13:33 Friday, May 9, 2025

ERROR: Argument now for word option sheet_interval not recognized. Expecting one of these keywords output, table, page, bygroup, 
       bygroups, proc, none or none.
37         ods excel options(sheet_name="Sh2" sheet_interval="now"); /*This tells SAS to start a new sheet for next PROCs*/
38         proc print data=sashelp.cars(obs=5);
39         run;

NOTE: There were 5 observations read from the data set SASHELP.CARS.
NOTE: PROCEDURE PRINT used (Total process time):
      real time           0.06 seconds
      user cpu time       0.06 seconds
      system cpu time     0.00 seconds
      memory              851.84k
      OS Memory           50200.00k
      Timestamp           05/09/2025 01:33:07 PM
      Step Count                        8351  Switch Count  0
      Page Faults                       0
      Page Reclaims                     127
      Page Swaps                        0
      Voluntary Context Switches        13
      Involuntary Context Switches      0
      Block Input Operations            0
      Block Output Operations           0
      

40         title 'Table2';
41         proc print data=sashelp.shoes(obs=3) noobs;
42         run;

NOTE: There were 3 observations read from the data set SASHELP.SHOES.
NOTE: PROCEDURE PRINT used (Total process time):
      real time           0.03 seconds
      user cpu time       0.03 seconds
      system cpu time     0.00 seconds
      memory              411.90k
      OS Memory           50456.00k
      Timestamp           05/09/2025 01:33:07 PM
      Step Count                        8352  Switch Count  0
      Page Faults                       0
      Page Reclaims                     28
      Page Swaps                        0
      Voluntary Context Switches        11
3                                                          The SAS System                               08:38 Wednesday, May 7, 2025

      Involuntary Context Switches      0
      Block Input Operations            0
      Block Output Operations           0
      

43         ods excel close;
NOTE: Writing EXCEL file: /usr/local/SAS/SASUsers/LabRet/UserDir/udclk79/q6.xlsx
44         
45         %LET _CLIENTTASKLABEL=;
46         %LET _CLIENTPROCESSFLOWNAME=;
47         %LET _CLIENTPROJECTPATH=;
48         %LET _CLIENTPROJECTPATHHOST=;
49         %LET _CLIENTPROJECTNAME=;
50         %LET _SASPROGRAMFILE=;
51         %LET _SASPROGRAMFILEHOST=;
52         
53         ;*';*";*/;quit;run;
54         ODS _ALL_ CLOSE;
55         
56         
57         QUIT; RUN;
58

Re: export 2 table into sheet1 and 2 tables into sheet 2 in one XLSX file

Ronein — Fri, 09 May 2025 10:43:59 GMT

One option no work! get error!

Any other solution to have 2 reports in Sheet1 and 2 reports in sheet2??

Re: export 2 table into sheet1 and 2 tables into sheet 2 in one XLSX file

Tom — Fri, 09 May 2025 14:21:53 GMT

What version of SAS are you using. SAS will display it at the top of the SAS log, but since you appear to be using something like SAS/Studio or Enterprise Guide to submit you SAS code finding the actual top of the SAS log might be difficult. So you can look at the value of the automatic macro variable SYSVLONG.

It really looks like a bug in SAS since the error message you show is confused, listing NONE twice.

ERROR: Argument now for word option sheet_interval not recognized. Expecting one of these keywords output, table, page, bygroup,
bygroups, proc, none or none.

Re: export 2 table into sheet1 and 2 tables into sheet 2 in one XLSX file

Ronein — Fri, 09 May 2025 14:50:33 GMT

So what do you recommend me to do?
I am using enterprise guide 8.3

Re: export 2 table into sheet1 and 2 tables into sheet 2 in one XLSX file

Tom — Fri, 09 May 2025 15:04:29 GMT

@Ronein wrote:
So what do you recommend me to do?
I am using enterprise guide 8.3

For ODS EXCEL it does not matter what user interface you are using to connect to SAS, but the version of SAS that you are currently using to run the code. There is probably a button in EG you can click on that will show you information about the version of SAS you are currently using. Otherwise add this line to your code:

%put &=sysvlong;

The example code works fine on SAS 9.4m7 release.

1    %put &=sysvlong;
SYSVLONG=9.04.01M7P080520
2    ods excel file="c:\downloads\ABC12.xlsx"
3      options(sheet_name="Sh1" sheet_interval="none")
4    ;
5    title 'Table1';
6    proc print data=sashelp.class(obs=3) noobs;
NOTE: Writing HTML Body file: sashtml1.htm
7    run;

NOTE: PROCEDURE PRINT used (Total process time):
      real time           0.38 seconds
      cpu time            0.03 seconds


8    title 'Table2';
9    proc print data=sashelp.shoes(obs=3) noobs;
10   run;

NOTE: PROCEDURE PRINT used (Total process time):
      real time           0.01 seconds
      cpu time            0.00 seconds


11
12   /*This tells SAS to start a new sheet for next PROCs*/
13   ods excel options(sheet_name="Sh2" sheet_interval="now");
14   proc print data=sashelp.cars(obs=5);
15   run;

NOTE: PROCEDURE PRINT used (Total process time):
      real time           0.02 seconds
      cpu time            0.00 seconds

You might try just re-typing the line. Perhaps some strange hidden character got into your source code somehow.

Note that the error message I get when I purposely use a wrong value for SHEET_INTERVAL does include NOW as a valid option.

WARNING: Argument noow for word option sheet_interval not recognized. Expecting one of these keywords
         output, table, page, bygroup, bygroups, proc, none, now or none.
21   ods excel options(sheet_name="Sh2" sheet_interval="noow");