Error---ODS EXCEL with now option

Ronein — Fri, 09 May 2025 17:41:01 GMT

Hello

I run this code using ODS EXCEL with now option.

I a using SAS enterprise guide 8.3.

Client version : '8.3.0.103' (When I run %put &_clientversion;)

My question- what is the reason for this error????


ods excel file="/usr/local/SAS/SASUsers/LabRet/UserDir/udclk79/Ex12.xlsx" options(sheet_interval="none");
proc print data=sashelp.class(where=(sex="M"));
run;
proc means data=sashelp.class(where=(sex="M"));
run;

ods excel options(sheet_interval="now");
proc print data=sashelp.class(where=(sex="F"));
run;
proc means data=sashelp.class(where=(sex="F"));
run;
ods excel close;

Here is the Log where can see the errors

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

1          ;*';*";*/;quit;run;
2          OPTIONS PAGENO=MIN;
3          %LET _CLIENTTASKLABEL='ODS EXCEL_b_Best!.sas';
4          %LET _CLIENTPROCESSFLOWNAME='Standalone Not In Project';
5          %LET _CLIENTPROJECTPATH='';
6          %LET _CLIENTPROJECTPATHHOST='';
7          %LET _CLIENTPROJECTNAME='';
8          %LET _SASPROGRAMFILE='M:\a.ריכוז תוכניות\Learn\ODS EXCEL_b_Best!.sas';
9          %LET _SASPROGRAMFILEHOST='HKSU102561A79';
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         ods excel file="/usr/local/SAS/SASUsers/LabRet/UserDir/udclk79/Ex12.xlsx" options(sheet_interval="none");
WARNING: Unsupported device 'SVG' for EXCEL destination. Using default device 'PNG'.
29         proc print data=sashelp.class(where=(sex="M"));
30         run;

NOTE: There were 10 observations read from the data set SASHELP.CLASS.
      WHERE sex='M';
NOTE: PROCEDURE PRINT used (Total process time):
      real time           0.12 seconds
      user cpu time       0.04 seconds
      system cpu time     0.00 seconds
      memory              2953.12k
      OS Memory           50200.00k
      Timestamp           05/09/2025 08:34:39 PM
      Step Count                        8508  Switch Count  0
      Page Faults                       0
      Page Reclaims                     187
      Page Swaps                        0
      Voluntary Context Switches        13
      Involuntary Context Switches      1
      Block Input Operations            0
      Block Output Operations           0
      

31         proc means data=sashelp.class(where=(sex="M"));
32         run;

NOTE: Multiple concurrent threads will be used to summarize data.
NOTE: There were 10 observations read from the data set SASHELP.CLASS.
2                                                          The SAS System                               08:38 Wednesday, May 7, 2025

      WHERE sex='M';
NOTE: PROCEDURE MEANS used (Total process time):
      real time           0.04 seconds
      user cpu time       0.03 seconds
      system cpu time     0.01 seconds
      memory              7087.29k
      OS Memory           56876.00k
      Timestamp           05/09/2025 08:34:39 PM
      Step Count                        8509  Switch Count  1
      Page Faults                       0
      Page Reclaims                     1741
      Page Swaps                        0
      Voluntary Context Switches        36
      Involuntary Context Switches      0
      Block Input Operations            0
      Block Output Operations           0
      

1                                                          The SAS System                                  20:34 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.
33         
34         ods excel options(sheet_interval="now");
35         proc print data=sashelp.class(where=(sex="F"));
36         run;

ERROR: In event 'output': Out of space writing to file 
       /usr/local/saswork/SAS_workD9C000003A20_LINX107717A13/SAS_workD57B00003A20_LINX107717A13/_T00007F31DAA2F460/xl/worksheets/she
       et1.xml.
stack traceback:
    [C]: in function 'error'
    ?: in function <?:127>
    (tail call): ?
    ?: in function 'close'
    ?: in function 'close'
    ?: in function 'finish_worksheet'
    ?: in function 'start_worksheet'
    ?: in function '?'
    ?: in function <?:508>
    (tail call): ?
ERROR: An error occurred during script execution. See the preceding messages.

NOTE: The SAS System stopped processing this step because of errors.
NOTE: There were 9 observations read from the data set SASHELP.CLASS.
      WHERE sex='F';
NOTE: PROCEDURE PRINT used (Total process time):
      real time           0.02 seconds
      user cpu time       0.01 seconds
      system cpu time     0.00 seconds
      memory              618.78k
      OS Memory           51736.00k
      Timestamp           05/09/2025 08:34:39 PM
      Step Count                        8510  Switch Count  6
      Page Faults                       0
      Page Reclaims                     18
      Page Swaps                        0
      Voluntary Context Switches        101
      Involuntary Context Switches      0
      Block Input Operations            0
      Block Output Operations           0
      


37         proc means data=sashelp.class(where=(sex="F"));
38         run;

NOTE: Multiple concurrent threads will be used to summarize data.
ERROR: In event 'output': ?:0: attempt to index field 'sheetdata' (a nil value)
stack traceback:
    ?: in function 'finish_worksheet'
    ?: in function 'start_worksheet'
    ?: in function '?'
    ?: in function <?:508>
    (tail call): ?
NOTE: The SAS System stopped processing this step because of errors.
NOTE: There were 9 observations read from the data set SASHELP.CLASS.
      WHERE sex='F';
NOTE: PROCEDURE MEANS used (Total process time):
      real time           0.01 seconds
      user cpu time       0.01 seconds
      system cpu time     0.01 seconds
      memory              6580.40k
3                                                          The SAS System                               08:38 Wednesday, May 7, 2025

      OS Memory           57644.00k
      Timestamp           05/09/2025 08:34:40 PM
      Step Count                        8511  Switch Count  1
      Page Faults                       0
      Page Reclaims                     1442
      Page Swaps                        0
      Voluntary Context Switches        34
      Involuntary Context Switches      0
      Block Input Operations            0
      Block Output Operations           0
      
ERROR: In event 'doc': ?:0: attempt to index field 'sheetdata' (a nil value)
stack traceback:
    ?: in function 'finish_worksheet'
    ?: in function '?'
    ?: in function <?:508>
    (tail call): ?
39         ods excel close;
NOTE: Writing EXCEL file: /usr/local/SAS/SASUsers/LabRet/UserDir/udclk79/Ex12.xlsx
40         
41         
42         %LET _CLIENTTASKLABEL=;
43         %LET _CLIENTPROCESSFLOWNAME=;
44         %LET _CLIENTPROJECTPATH=;
45         %LET _CLIENTPROJECTPATHHOST=;
46         %LET _CLIENTPROJECTNAME=;
47         %LET _SASPROGRAMFILE=;
48         %LET _SASPROGRAMFILEHOST=;
1                                                          The SAS System                                  20:34 Friday, May 9, 2025

ERROR: Calling global tagset function 'detach_stream':
Out of space writing to file /usr/local/saswork/SAS_workD9C000003A20_LINX107717A13/#LN05564.
stack traceback:
    [C]: in function 'error'
    ?: in function <?:127>
    (tail call): ?
    ?: in function 'close'
    ?: in function 'detach'
    ?: in function <?:43>
49         
50         ;*';*";*/;quit;run;
51         ODS _ALL_ CLOSE;
ERROR: An error occurred during script execution. See the preceding messages.
52         
53         
54         QUIT; RUN;
55

Re: Error---ODS EXCEL with now option

PaigeMiller — Fri, 09 May 2025 17:55:30 GMT

What is the value of macro variable &sysvlong?

Also, we don't need to see 100 lines of the log before the first ODS EXCEL, and we don't need to see the lines after the error.

Re: Error---ODS EXCEL with now option

SASKiwi — Fri, 09 May 2025 23:04:44 GMT

From the documentation:

In SAS 9.4M5, the ODS EXCEL statement supports the following options:

You need to be using at least 9.4M5 to use NOW.

Re: Error---ODS EXCEL with now option

Tom — Fri, 09 May 2025 23:24:12 GMT

Show the value of SYSVLONG, not _CLIENTVERSION.

It does not matter what version of Enterprise Guide you are using to edit your SAS code. Just like it would not matter what version of EMACS or VSCode you were using to edit your SAS code.

Re: Error---ODS EXCEL with now option

Ksharp — Sat, 10 May 2025 03:19:57 GMT

I got no problem with that code.

But another workaround way is creating a DUMMY proc by 'ods select none' to reset the option 'sheet_interval='.

And it is mentioned in this:

https://support.sas.com/kb/57/766.html

ods excel file="c:\temp\temp.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 select none;
ods excel options(sheet_name="xx" sheet_interval="proc"); /*This tells SAS to start a new sheet for next PROCs*/
proc print data=sashelp.class;run;
ods select all;



ods excel options(sheet_name="Sh2" sheet_interval="none"); /*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;

topic Error---ODS EXCEL with now option in SAS Programming

Error---ODS EXCEL with now option

Re: Error---ODS EXCEL with now option

Re: Error---ODS EXCEL with now option

Re: Error---ODS EXCEL with now option

Re: Error---ODS EXCEL with now option