topic Re: how to export graph into worksheet in SAS Programming

how to export graph into worksheet

radha009 — Mon, 23 Apr 2018 02:33:07 GMT

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;

Re: how to export graph into worksheet

Reeza — Mon, 23 Apr 2018 02:35:30 GMT

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.

Re: how to export graph into worksheet

radha009 — Mon, 23 Apr 2018 03:13:33 GMT

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.

Re: how to export graph into worksheet

Reeza — Mon, 23 Apr 2018 03:23:34 GMT

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.

Re: how to export graph into worksheet

radha009 — Mon, 23 Apr 2018 04:05:24 GMT

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;

Re: how to export graph into worksheet

Reeza — Mon, 23 Apr 2018 04:08:08 GMT

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.

Re: how to export graph into worksheet

radha009 — Mon, 23 Apr 2018 04:14:32 GMT

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

Re: how to export graph into worksheet

Reeza — Mon, 23 Apr 2018 04:17:16 GMT

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;

Re: how to export graph into worksheet

Reeza — Mon, 23 Apr 2018 04:18:41 GMT

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

Re: how to export graph into worksheet

radha009 — Mon, 23 Apr 2018 04:30:13 GMT

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;

Re: how to export graph into worksheet

radha009 — Mon, 23 Apr 2018 13:54:45 GMT

resolved in adding below code:

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

Re: how to export graph into worksheet

Reeza — Mon, 23 Apr 2018 14:42:13 GMT

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