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

The graph is not exporting to excel all datasets are displaying

 

ods tagsets.excelxp file="C:\Dashboard\SAS\Dashboard.xml" style=styles.excel_update
options(sheet_name='Ent' embedded_titles="yes" sheet_interval="none");
proc report data=dash.Ent_AllInc_Final;
run;
proc report data=dash.ENT_L2Inc_Final;
run;
proc report data=dash.Ent_pie_Final;
run;
proc report data=dash.Ent_Top_apps;
run;
proc report data=dash.Ent_change_req;
run;
proc sgplot data=dash.Ent_Top_apps;
vbar Affected_CI / reponse=count stat=mean fillattrs=(color=lightblue) barwidth= .25;
xaxis display=(nolabel);
yaxis display=(nolabel) grid;
run;
ods excel close;

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
radha009
Quartz | Level 8

resolved in adding below code:

 

ods excel options(sheet_interval="table");
ods exclude all;
data _null_;
file print;
put _all_;
run;
ods select all;

View solution in original post

11 REPLIES 11
Reeza
Super User

You're mixing and matching there, you open with ODS TAGSETS.EXCELXP and closing with ODS EXCEL. 

 

TAGSETS.EXCELXP does not support graphics, ODS EXCEL will do it by default. 

radha009
Quartz | Level 8

i started writing my code with excel and then changed to excelxp due to i have 3 datasets & 1 graph  to display in multiple sheets,

sheet 1( 3 datasets and graph) sheet2 ( 3 datasets and graph) sheet 3 ( 2 datasets and 2 graph)

with excel all data is displaying in one sheet.so i changed to excelxp the multiplesheet issue is resolved but graphs are not displaying.

 

How to display datasets and graph in multiple sheets. Thanks a lot in advance.

 

Reeza
Super User

Did you run your code with ODS EXCEL? 

The SGPLOT will output directly to the Excel file...I'm not understanding the issue. 

 

TAGSETS.EXCELXP generates an XML file and XML does not support images, so you cannot use that. The features you're mentioning, multiple worksheets, are available in ODS Excel as well. Search for the papers by Chevell Parker regarding ODS EXCEL and you'll find examples and demo code. So far, there's nothing in your code that shows an attempt at generating multi sheets, in fact, you've explicitly chosen a single sheet with sheet_interval='none'.


@radha009 wrote:

i started writing my code with excel and then changed to excelxp due to i have 3 datasets & 1 graph  to display in multiple sheets,

sheet 1( 3 datasets and graph) sheet2 ( 3 datasets and graph) sheet 3 ( 2 datasets and 2 graph)

with excel all data is displaying in one sheet.so i changed to excelxp the multiplesheet issue is resolved but graphs are not displaying.

 

How to display datasets and graph in multiple sheets. Thanks a lot in advance.

 


 

radha009
Quartz | Level 8

with excel the graph is working fine but it is displaying different sheet.

 

ods listing close;
ods excel file="C:\Dashboard\SAS\Dash2.xlsx"  
options(sheet_name='Ent_BTO') style=styles.sansprinter;;
proc report data=dash.Ent_AllInc_Final;
run;
proc report data=dash.ENT_L2Inc_Final;
run;
proc report data=dash.Ent_Top_apps;
run;
ods graphics / height=600 width=1000 noborder;
proc sgplot data=dash.Ent_Top_apps;
vbar Affected_CI / reponse=count stat=mean fillattrs=(color=lightblue) barwidth= .25;
xaxis display=(nolabel);
yaxis display=(nolabel) grid;
run;
ods excel options(sheet_name='ICM_BTO' embedded_titles="yes" );
proc report data=dash.ICM_AllInc_Final;
run;
proc report data=dash.ICM_Top_apps;
run;
ods graphics / height=600 width=1000 noborder;
proc sgplot data=dash.ICM_Top_apps;
vbar Affected_CI / reponse=count stat=mean fillattrs=(color=lightblue) barwidth= .25;
xaxis display=(nolabel);
yaxis display=(nolabel) grid;
run;
proc report data=dash.ICM_change_req;
run;
ods excel options(sheet_name='MF_BTO' embedded_titles="yes" );
proc report data=dash.MF_AllInc_Final;
run;
proc report data=dash.MF_L2Inc_Final;
run;
proc report data=dash.MF_pie_Final;
run;
ods graphics / height=600 width=1000 noborder;
proc sgplot data=dash.MF_Top_apps;
vbar Affected_CI / reponse=count stat=mean fillattrs=(color=lightblue) barwidth= .25;
xaxis display=(nolabel);
yaxis display=(nolabel) grid;
run;
proc report data=dash.MF_Major_INC;
run;
ods excel options(sheet_name='SFSS_BTO' embedded_titles="yes" );
proc report data=dash.SFSS_AllInc_Final;
run;
proc report data=dash.SFSS_L2Inc_Final;
run;
proc report data=dash.SFSS_pie_Final;
run;

ods excel close;
ods listing;
run;

Reeza
Super User

I cannot see your output, log or run your code. What does the following mean?

 

with excel the graph is working fine but it is displaying different sheet.

radha009
Quartz | Level 8

excel.jpg

 

 

expecting one Ent_BTO ,ICM_BTO tabs with datasets and graph in each sheet.

Reeza
Super User

@radha009 please take the time to add more details to your questions initially. It helps us understand what you want and will get you better answers. 

Reeza
Super User

You aren't controlling your sheet_interval properly. 

 

To keep all output on one sheet initially set sheet_interval='none'. 


But then when you want to start a new sheet, you need to specify that, sheet_interval='now' will create a new sheet. See the example below. There are many examples on line as well. 

 

ods excel file='/folders/myfolders/demo.xlsx' style=meadow

/*Sets sheet name and no new sheet unless set*/
options (sheet_interval='none' sheet_name="Reeza1" embedded_titles='yes');
title 'Class data';

proc print data=sashelp.class;
run;

title 'Cars data';

proc print data=sashelp.cars (obs=20);
run;

title;
proc sgplot data=sashelp.cars;
    scatter x=mpg_city y=mpg_highway;
run;

/*This options tells SAS to starts a new sheet*/
ods excel options (sheet_interval='now' sheet_name='Reeza2');
title 'Air data';

proc print data=sashelp.air;
run;

title 'Shoes data';

proc print data=sashelp.shoes(obs=20);
run;

title;
proc sgplot data=sashelp.class;
    scatter x=height y=weight / group=sex;
run;

ods excel close;
radha009
Quartz | Level 8

NOTE: There were 3 observations read from the data set DASH.ICM_MAJOR_INC.
NOTE: PROCEDURE REPORT used (Total process time):
      real time           0.07 seconds
      cpu time            0.03 seconds


ERROR: Argument now for word option sheet_interval not recognized. Expecting one of these
       keywords output, table, page, bygroup, bygroups, proc, none or none.
12015  ods excel options(sheet_name='MF_BTO' embedded_titles="yes" sheet_interval='now' );
12016  title "All Incidents - MF by Group";
12017  proc report data=dash.MF_AllInc_Final;

radha009
Quartz | Level 8

resolved in adding below code:

 

ods excel options(sheet_interval="table");
ods exclude all;
data _null_;
file print;
put _all_;
run;
ods select all;

Reeza
Super User

@radha009 that's only required if you're on an earlier version of SAS (ie 9.4 TS1M3 or lower). 

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 11 replies
  • 2405 views
  • 0 likes
  • 2 in conversation