DATA Step, Macro, Functions and more

how to export graph into worksheet

Accepted Solution Solved
Reply
Contributor
Posts: 65
Accepted Solution

how to export graph into worksheet

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;

 

 


Accepted Solutions
Solution
a month ago
Contributor
Posts: 65

Re: how to export graph into worksheet

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


All Replies
Super User
Posts: 22,850

Re: how to export graph into worksheet

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. 

Contributor
Posts: 65

Re: how to export graph into worksheet

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.

 

Super User
Posts: 22,850

Re: how to export graph into worksheet

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.

 


 

Contributor
Posts: 65

Re: how to export graph into worksheet

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;

Super User
Posts: 22,850

Re: how to export graph into worksheet

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.

Contributor
Posts: 65

Re: how to export graph into worksheet

excel.jpg

 

 

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

Super User
Posts: 22,850

Re: how to export graph into worksheet

@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. 

Super User
Posts: 22,850

Re: how to export graph into worksheet

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;
Contributor
Posts: 65

Re: how to export graph into worksheet

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;

Solution
a month ago
Contributor
Posts: 65

Re: how to export graph into worksheet

resolved in adding below code:

 

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

Super User
Posts: 22,850

Re: how to export graph into worksheet

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

☑ This topic is solved.

Need further help from the community? Please ask a new question.

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