topic Re: How to include graphics in excel via Stored Process in Developers

How to include graphics in excel via Stored Process

exj — Thu, 17 Oct 2013 09:48:47 GMT

Hello,

I don't know how is it possible to include graphics from Stored Process output to excel. I know that tagset msoffice2k might be key for this problem, but I don't know how to use it either. I think I have tried all what I know (including stream / package -option in Stored Process, content-type etc. ). I have obtained that webpage prompts me the window and asks what I want to do with my excel (open or save). After that in gives warning concerning xml-file named as xls-file. Finally there is a graph in excel and I can click and point those bars but there is a warning sign on that graph that says something like "Graph isn't available now".

I would appreciate If somebody can help me with this problem. Thank you in advance for your comments and activity!

I have this kind of code:

----------------------------------
%macro stylemacro();
data _null_;
%if &printoutput eq HTML %then
%do;
%let cssstyle = minimal;
%end;

%if &tulostus eq XLS %then %do;
%let cssstyle = minimal;
%let old = %sysfunc(stpsrv_header(Content-type, application/vnd.ms-excel));
*rc = stpsrv_header('Content-type','application/vnd.ms-excel');
/* rc = stpsrv_header('Content-disposition','attachment; filename=test.xls');*/
%end;

%if &printoutput eq PDF or &printoutput eq RTF %then %do;
%let cssstyle = RTF;
%end;
run;
%mend;

%macro handleParms();
%let _ODSDEST= &printoutput;
%if &printoutput eq XLS %then %let _ODSDEST=HTML;
*%if &printoutput eq XLS %then %let _ODSDEST=TAGSETS.MSOFFICE2K;

%mend;

%macro chooseOutput;
ods &_ODSDEST file=_WEBOUT Style=&cssstyle;

*ods html file=_webout;

options center nodate;

%if &outputOut=table %then %do;

proc tabulate data=sashelp.class ;

class sex ;
table sex ;

run;

%end;

%if &outputOut=graphic %then %do;

proc sort data=sashelp.class out=class; by sex age; run;

proc gchart data=class;

vbar3d age / group=sex

discrete

nozero

shape=cylinder

patternid=group;

run;

%end;

%mend outputOut;

%stylemacro();
%handleParms();

%stpbegin;
%outputOut;
%stpend;

Re: How to include graphics in excel via Stored Process

NathanOch — Fri, 12 Jun 2015 19:16:10 GMT

I have having the same difficulty. Streaming tabular information to Excel is very easy, however, getting any graphical results to go with it is next to impossible.

It seems this topic is not properly discussed. I would love to see a step by step operation of how to stream graphics along with text from the Information Delivery Portal to Excel.

Nathan Och

Re: How to include graphics in excel via Stored Process

Vince_SAS — Mon, 15 Jun 2015 12:21:52 GMT

The Excel destination is available starting in the soon-to-be-released SAS 9.4 Maintenance Level 3.

ods _all_ close;

%let RV=%sysfunc(stpsrv_header(Content-type,application/vnd.openxmlformats-officedocument.spreadsheetml.sheet));
%let RV=%sysfunc(stpsrv_header(Content-disposition,attachment; filename= "Class.xlsx"));

ods Excel file=_webout style=Excel;
proc print data=sashelp.class noobs; run; quit;
proc gchart data=sashelp.class;
vbar age / discrete;
run; quit;

ods Excel close;

Vince DelGobbo

SAS R&D

Re: How to include graphics in excel via Stored Process

Catrin — Fri, 06 Nov 2015 08:46:44 GMT

Hallo,

In SAS 9.4 Maintenance Level 1 ODS EXCEL is experimental. So I used the original code from Vince to look if it is already working the right way.

I got the following ERROR in the LOG:

12        +
13        +*ProcessBody;
14        +*  Ende des EG-generierten Codes (diese Zeile nicht bearbeiten);
15        +
16        +
17        +ods _all_ close;
18        +
19        +
20        +%let RV=%sysfunc(stpsrv_header(Content-type,application/vnd.openxmlformats-officedocument.spreadsheetml.sheet));
21        +%let RV=%sysfunc(stpsrv_header(Content-disposition,attachment; filename= "Class.xlsx"));
22        +
23        +
24        +ods Excel file=_webout ;
ERROR: The package file output specification must be the name of a file on disk or a fileref assigned to a file on disk.
ERROR: No body file. EXCEL output will not be created.
25        +  proc print data=sashelp.class noobs; run;
NOTE: SAS set option OBS=0 and will continue to check statements. This might cause NOTE: No observations in data set.
NOTE: PROCEDURE PRINT used (Total process time):
real time           0.00 seconds
cpu time            0.00 seconds

25       !+                                            quit;
26        +  proc gchart data=sashelp.class;
27        +    vbar age / discrete;
28        +  run;
The SAS System
WARNING: RUN statement ignored due to previous errors. Submit QUIT; to terminate the procedure.
28       !+       quit;
NOTE: PROCEDURE GCHART used (Total process time):
real time           0.00 seconds
cpu time            0.00 seconds

29        +
30        +ods Excel close;
31        +

Is the Maitenance Level the reason it does not work?

I started the Stored Process from the SAS Statistic Portal.
2. The Portal Page does look like the Stored Process is still running, although it stopped by ERROR. Please find the attached picture.
Could you please help me in understanding the behaviour?

Thanks in advance Catrin

Re: How to include graphics in excel via Stored Process

Vince_SAS — Fri, 06 Nov 2015 14:52:05 GMT

The code that I posted works with a SAS Stored Process created using SAS 9.4 M3.

Vince DelGobbo

SAS R&D