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

Ronein · Posted 09-24-2021 07:05 AM

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;

Ksharp · Posted 09-24-2021 08:17 AM

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;

View solution in original post

PaigeMiller · Posted 09-24-2021 07:20 AM

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

--
Paige Miller

Ronein · Posted 09-24-2021 07:21 AM

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;

PaigeMiller · Posted 09-24-2021 07:24 AM

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.

--
Paige Miller

Ronein · Posted 09-24-2021 07:28 AM

;

PaigeMiller · Posted 09-24-2021 07:30 AM

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

--
Paige Miller

Ronein · Posted 09-24-2021 07:31 AM

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

PaigeMiller · Posted 09-24-2021 07:40 AM

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?

--
Paige Miller

andreas_lds · Posted 09-24-2021 08:05 AM

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

Ronein · Posted 09-26-2021 01:09 AM

I get 9.04.01M4P110916

Oligolas · Posted 09-24-2021 09:13 AM

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.

________________________
- Cheers -

Ksharp · Posted 09-24-2021 08:17 AM

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;

Ronein · Posted 09-24-2021 09:15 AM

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;

Ksharp · Posted 09-24-2021 09:18 AM

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 .

SAS Innovate 2025: Call for Content

Classroom Training Available!