topic Re: Export to XLSX Excel :2 sheets and 3 tables in each sheet in SAS Programming

Export to XLSX Excel :2 sheets and 3 tables in each sheet

Ronein — Fri, 24 Sep 2021 11:15:51 GMT

Hello

I want to export 6 tables into Excel (XLSX) in the following way:

3 tables in sheet1

3 tables in sheet2

I have tried to create it and I haven't received the desired result.
May anyone show the correct way
thanks so much


/****I get Sheet1 with 3 tables(correct) and 3 other sheets with 1 table in each sheet***/
/***I want to get Sheet1 with 3 tables and Sheet2 with 3 tables***/
ods excel file="/path/report.xlsx"
options(embedded_titles='yes' sheet_name="SH1" sheet_interval="none");
title;
title 'BMW';
proc print data=SASHELP.CARS(WHERE=(compress(make)='BMW')) noobs;
run;
title1 '';
title2 'Jaguar';
proc print data=SASHELP.CARS(WHERE=(compress(make)='Jaguar')) noobs;
run;
title1 '';
title2 'Lincoln';
proc print data=SASHELP.CARS(WHERE=(compress(make)='Lincoln')) noobs;
run;


ods excel options(embedded_titles='yes'  sheet_name="SH2" sheet_interval="table");
title;
title 'Sedan';
proc print data=SASHELP.cars(WHERE=(compress(Type)='Sedan' )) noobs;
run;
title1 '';
title2 'Sports';
proc print data=SASHELP.cars(WHERE=(compress(Type)='Sports' )) noobs;
run;
title1 '';
title2 'SUV';
proc print data=SASHELP.CLASS(WHERE=(compress(Type)='SUV' )) noobs;
run;
ods excel close;

Re: Export to XLSX Excel :2 sheets and 3 tables in each sheet

PaigeMiller — Fri, 24 Sep 2021 11:20:07 GMT

When you create sheet named SH2, use sheet_interval="now"

Re: Export to XLSX Excel :2 sheets and 3 tables in each sheet

Ronein — Fri, 24 Sep 2021 11:24:16 GMT

Thanks, however please see the error when using "now"
ERROR: Argument now for word option sheet_interval not recognized. Expecting one of these keywords output, table, page, bygroup,
bygroups, proc, none or none.

/***Get error about sheet_interval="now" ***/
ods excel file="/path/report.xlsx"
options(embedded_titles='yes' sheet_name="SH1" sheet_interval="none");
title;
title 'BMW';
proc print data=SASHELP.CARS(WHERE=(compress(make)='BMW')) noobs;
run;
title1 '';
title2 'Jaguar';
proc print data=SASHELP.CARS(WHERE=(compress(make)='Jaguar')) noobs;
run;
title1 '';
title2 'Lincoln';
proc print data=SASHELP.CARS(WHERE=(compress(make)='Lincoln')) noobs;
run;


ods excel options(embedded_titles='yes'  sheet_name="SH2"  sheet_interval="now");
title;
title 'Sedan';
proc print data=SASHELP.CARS(WHERE=(compress(Type)='Sedan' )) noobs;
run;
title1 '';
title2 'Sports';
proc print data=SASHELP.CARS(WHERE=(compress(Type)='Sports' )) noobs;
run;
title1 '';
title2 'SUV';
proc print data=SASHELP.CARS(WHERE=(compress(Type)='SUV' )) noobs;
run;
ods excel close;

Re: Export to XLSX Excel :2 sheets and 3 tables in each sheet

PaigeMiller — Fri, 24 Sep 2021 11:24:44 GMT

I do not get an error. As always, we need to see the LOG showing your code and the ERROR messages, formatted properly by clicking on the </> icon and pasting the log into that window.

Re: Export to XLSX Excel :2 sheets and 3 tables in each sheet

Ronein — Fri, 24 Sep 2021 11:32:12 GMT

;

Re: Export to XLSX Excel :2 sheets and 3 tables in each sheet

PaigeMiller — Fri, 24 Sep 2021 11:30:00 GMT

Please, I asked you to format the log properly by clicking on the </> icon and pasting it into the window that appears.

Re: Export to XLSX Excel :2 sheets and 3 tables in each sheet

Ronein — Fri, 24 Sep 2021 11:31:18 GMT

1                                                          The SAS System                           13:10 Friday, September 24, 2021

1          ;*';*";*/;quit;run;
2          OPTIONS PAGENO=MIN;
3          %LET _CLIENTTASKLABEL='Program (2)';
4          %LET _CLIENTPROCESSFLOWNAME='Process Flow';
5          %LET _CLIENTPROJECTPATH='';
6          %LET _CLIENTPROJECTPATHHOST='';
7          %LET _CLIENTPROJECTNAME='';
8          %LET _SASPROGRAMFILE='';
9          %LET _SASPROGRAMFILEHOST='';
10         
11         ODS _ALL_ CLOSE;
12         OPTIONS DEV=PNG;
13         GOPTIONS XPIXELS=0 YPIXELS=0;
14         FILENAME EGSR TEMP;
15         ODS tagsets.sasreport13(ID=EGSR) FILE=EGSR
16             STYLE=HtmlBlue
17             STYLESHEET=(URL="file:///C:/Program%20Files/SASHome94/SASEnterpriseGuide/7.1/Styles/HtmlBlue.css")
18             NOGTITLE
19             NOGFOOTNOTE
20             GPATH=&sasworklocation
21             ENCODING=UTF8
22             options(rolap="on")
23         ;
NOTE: Writing TAGSETS.SASREPORT13(EGSR) Body file: EGSR
24         
25         GOPTIONS ACCESSIBLE;
26         /***Get error about sheet_interval="now" ***/
27         ods excel file="/usr/local/SAS/SASUsers/LabRet/UserDir/report.xlsx"
28         options(embedded_titles='yes' sheet_name="SH1" sheet_interval="none");
29         title;
30         title 'BMW';
31         proc print data=SASHELP.CARS(WHERE=(compress(make)='BMW')) noobs;
32         run;

NOTE: There were 20 observations read from the data set SASHELP.CARS.
      WHERE COMPRESS(make)='BMW';
NOTE: PROCEDURE PRINT used (Total process time):
      real time           0.09 seconds
      user cpu time       0.09 seconds
      system cpu time     0.00 seconds
      memory              3776.53k
      OS Memory           35012.00k
      Timestamp           09/24/2021 02:30:49 PM
      Step Count                        156  Switch Count  0
      Page Faults                       0
      Page Reclaims                     523
      Page Swaps                        0
      Voluntary Context Switches        8
      Involuntary Context Switches      0
      Block Input Operations            0
      Block Output Operations           0
      

33         title1 '';
34         title2 'Jaguar';
35         proc print data=SASHELP.CARS(WHERE=(compress(make)='Jaguar')) noobs;
36         run;

2                                                          The SAS System                           13:10 Friday, September 24, 2021

NOTE: There were 12 observations read from the data set SASHELP.CARS.
      WHERE COMPRESS(make)='Jaguar';
NOTE: PROCEDURE PRINT used (Total process time):
      real time           0.05 seconds
      user cpu time       0.05 seconds
      system cpu time     0.00 seconds
      memory              838.25k
      OS Memory           35268.00k
      Timestamp           09/24/2021 02:30:49 PM
      Step Count                        157  Switch Count  0
      Page Faults                       0
      Page Reclaims                     64
      Page Swaps                        0
      Voluntary Context Switches        28
      Involuntary Context Switches      1
      Block Input Operations            0
      Block Output Operations           0
      

37         title1 '';
38         title2 'Lincoln';
39         proc print data=SASHELP.CARS(WHERE=(compress(make)='Lincoln')) noobs;
40         run;

NOTE: There were 9 observations read from the data set SASHELP.CARS.
      WHERE COMPRESS(make)='Lincoln';
NOTE: PROCEDURE PRINT used (Total process time):
      real time           0.04 seconds
      user cpu time       0.04 seconds
      system cpu time     0.00 seconds
      memory              723.06k
      OS Memory           35524.00k
      Timestamp           09/24/2021 02:30:49 PM
      Step Count                        158  Switch Count  0
      Page Faults                       0
      Page Reclaims                     34
      Page Swaps                        0
      Voluntary Context Switches        9
      Involuntary Context Switches      0
      Block Input Operations            0
      Block Output Operations           0
      

1                                                          The SAS System                           14:30 Friday, September 24, 2021

ERROR: Argument now for word option sheet_interval not recognized. Expecting one of these keywords output, table, page, bygroup, 
       bygroups, proc, none or none.
41         
42         
43         ods excel options(embedded_titles='yes'  sheet_name="SH2"  sheet_interval="now");
44         title;
45         title 'Sedan';
46         proc print data=SASHELP.CARS(WHERE=(compress(Type)='Sedan' )) noobs;
47         run;

NOTE: There were 262 observations read from the data set SASHELP.CARS.
      WHERE COMPRESS(Type)='Sedan';
NOTE: PROCEDURE PRINT used (Total process time):
      real time           0.90 seconds
      user cpu time       0.89 seconds
      system cpu time     0.01 seconds
      memory              15740.15k
3                                                          The SAS System                           13:10 Friday, September 24, 2021

      OS Memory           52932.00k
      Timestamp           09/24/2021 02:30:50 PM
      Step Count                        159  Switch Count  3
      Page Faults                       0
      Page Reclaims                     3848
      Page Swaps                        0
      Voluntary Context Switches        27
      Involuntary Context Switches      3
      Block Input Operations            0
      Block Output Operations           0
      

48         title1 '';
49         title2 'Sports';
50         proc print data=SASHELP.CARS(WHERE=(compress(Type)='Sports' )) noobs;
51         run;

NOTE: There were 49 observations read from the data set SASHELP.CARS.
      WHERE COMPRESS(Type)='Sports';
NOTE: PROCEDURE PRINT used (Total process time):
      real time           0.19 seconds
      user cpu time       0.19 seconds
      system cpu time     0.00 seconds
      memory              1755.31k
      OS Memory           39364.00k
      Timestamp           09/24/2021 02:30:50 PM
      Step Count                        160  Switch Count  1
      Page Faults                       0
      Page Reclaims                     141
      Page Swaps                        0
      Voluntary Context Switches        19
      Involuntary Context Switches      1
      Block Input Operations            0
      Block Output Operations           0
      

52         title1 '';
53         title2 'SUV';
54         proc print data=SASHELP.CARS(WHERE=(compress(Type)='SUV' )) noobs;
55         run;

NOTE: There were 60 observations read from the data set SASHELP.CARS.
      WHERE COMPRESS(Type)='SUV';
NOTE: PROCEDURE PRINT used (Total process time):
      real time           0.21 seconds
      user cpu time       0.21 seconds
      system cpu time     0.00 seconds
      memory              2267.50k
      OS Memory           40132.00k
      Timestamp           09/24/2021 02:30:50 PM
      Step Count                        161  Switch Count  1
      Page Faults                       0
      Page Reclaims                     230
      Page Swaps                        0
      Voluntary Context Switches        16
      Involuntary Context Switches      0
      Block Input Operations            0
      Block Output Operations           0
4                                                          The SAS System                           13:10 Friday, September 24, 2021

      

56         ods excel close;
NOTE: Writing EXCEL file: /usr/local/SAS/SASUsers/LabRet/UserDir/report.xlsx
57         
58         
59         
60         GOPTIONS NOACCESSIBLE;
61         %LET _CLIENTTASKLABEL=;
62         %LET _CLIENTPROCESSFLOWNAME=;
63         %LET _CLIENTPROJECTPATH=;
64         %LET _CLIENTPROJECTPATHHOST=;
65         %LET _CLIENTPROJECTNAME=;
66         %LET _SASPROGRAMFILE=;
67         %LET _SASPROGRAMFILEHOST=;
68         
69         ;*';*";*/;quit;run;
70         ODS _ALL_ CLOSE;
71         
72         
73         QUIT; RUN;
74

Re: Export to XLSX Excel :2 sheets and 3 tables in each sheet

PaigeMiller — Fri, 24 Sep 2021 11:40:57 GMT

It works fine for me. I am guessing that perhaps you might have an earlier version of SAS perhaps (that's the only thing I can think of).

When I run

%put &sysvlong;

I get

9.04.01M5P091317

What do you get?

Re: Export to XLSX Excel :2 sheets and 3 tables in each sheet

andreas_lds — Fri, 24 Sep 2021 12:05:20 GMT

Afaik sheet_interval="now" was added in version 9.4m5.

Re: Export to XLSX Excel :2 sheets and 3 tables in each sheet

Ksharp — Fri, 24 Sep 2021 12:17:49 GMT

You need reset these options before the second sheet.

ods excel file="c:\temp\report.xlsx"
options(embedded_titles='yes' sheet_name="SH1" sheet_interval="none");
title;
title 'BMW';
proc print data=SASHELP.CARS(WHERE=(compress(make)='BMW')) noobs;
run;
title1 '';
title2 'Jaguar';
proc print data=SASHELP.CARS(WHERE=(compress(make)='Jaguar')) noobs;
run;
title1 '';
title2 'Lincoln';
proc print data=SASHELP.CARS(WHERE=(compress(make)='Lincoln')) noobs;
run;




ods select none;
ods excel options( sheet_interval="proc");
proc print data=sashelp.class;run;
ods select all;





ods excel options(embedded_titles='yes'  sheet_name="SH2" sheet_interval="none");
title;
title 'Sedan';
proc print data=SASHELP.cars(WHERE=(compress(Type)='Sedan' )) noobs;
run;
title1 '';
title2 'Sports';
proc print data=SASHELP.cars(WHERE=(compress(Type)='Sports' )) noobs;
run;
title1 '';
title2 'SUV';
proc print data=SASHELP.cars(WHERE=(compress(Type)='SUV' )) noobs;
run;
ods excel close;

Re: Export to XLSX Excel :2 sheets and 3 tables in each sheet

Oligolas — Fri, 24 Sep 2021 13:13:37 GMT

the 'now' option is only available since the TS1M5 release of SAS9.4

I'm afraid you'll need to use the good old excelxp tagset to accomplish these results and convert the xml afterward to a native xlsx document.

Re: Export to XLSX Excel :2 sheets and 3 tables in each sheet

Ronein — Fri, 24 Sep 2021 13:15:34 GMT

Thank you,
I haven't asked to print from aashelp.class and only asked to print from sashelp.cars.
What is the purpose of following statements please??
ods select none;
ods excel options( sheet_interval="proc");
proc print data=sashelp.class;run;
ods select all;

Re: Export to XLSX Excel :2 sheets and 3 tables in each sheet

Ksharp — Fri, 24 Sep 2021 13:18:14 GMT

These are just dummy code, would not print anything due to "ODS SELECT NONE;" ,
just for resetting options "Sheet_interval=" ,and make it reactive "Sheet_interval=none" again .

Re: Export to XLSX Excel :2 sheets and 3 tables in each sheet

Ronein — Sun, 26 Sep 2021 05:09:17 GMT

I get 9.04.01M4P110916