topic Re: Stored Process - output to XLSX in Developers

Stored Process - output to XLSX

AMUMOR_SAS — Wed, 31 May 2017 10:25:32 GMT

Hi. Now I need help. I am running two kinds of StP, Stand alone and Chained. In both types I want the Proc Report to make a XLSX output.

When I run the Stand alone StP, I get my XLSX output and I can open it without any errormessages.

But when I run the Chained StP, the XLSX is produced, but when I try to open it, I get en error message which tells me that the XLSX file contains unreadable contents. Why?

For the test, in both StPs I make the output from sashelp.class.

My codes are shown below.

/*Stand alone StP - this works :*/
%global OUTPUT;

*ProcessBody;

%MACRO Eksport;

%if &Output. = EXCEL %then %do;

	data _null_;
		rc = stpsrv_header('Content-type','application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');

		rc = stpsrv_header('Content-disposition','attachment; filename=Test.xlsx');
	Run;
%End;
%Mend
%Eksport;

%Macro Print;
%if &Output. = EXCEL %then %do;

	ods excel file = _webout style=Plateau
		options (Sheet_label="IK"
					embedded_titles="yes"
					embed_titles_once="no");

	Title color=green "Test";
	proc print label data=sashelp.class noobs
		style (header) =[background=aliceblue]
		style (column) = [font_size=1.5];
	run;
	ods excel close;
%End;
%Mend;
%Print;

/*Chained StP - this does not work for XLSX, but it works for HTML-output:
The MAIN StP:*/

%global Output;

*ProcessBody;

%macro PUTMENU;

data _null_;
	file _webout encoding='UTF-8';
	put "<h1 style=color:ForestGreen align=center> Test of XLSX output";
	put "</h1>";
	put "<div Align=Center>";
	put "<body>";
	put "<form ACTION='&_URL'>";
	put "<input TYPE='HIDDEN' NAME='_action' value='form,properties,execute,nobanner,newwindow'>";
	put "<input TYPE='hidden' NAME='_program' VALUE='&_Program._Print'>";

	put "<table BORDER=1 CELLSPACING=1 CELLPADDING=4 WIDTH='30%'>";
	put "<tr BGCOLOR='ForestGreen'>";
run;

data _null_;
	file _webout;
	put "</select>";
	put "</tr></table><P>";
	put "<input type='SUBMIT' name='valg' value='HTML'>";
	put "<input type='SUBMIT' name='valg' value='EXCEL'>";
	put "<input type='RESET' value='Tøm meny'>";
	put "</body></div></html>";
run;

%Mend;
%PutMenu;

/*The Evoked StP:*/

%global Output;

*ProcessBody;

%MACRO Eksport;

	%if &Output. = EXCEL %then %do;

		ods excel file = _webout;

		data _null_;

		rc = stpsrv_header('Content-type','application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');

		rc = stpsrv_header('Content-disposition','attachment; filename=Interne konti med siste transaksjon.xlsx');
		  
		run;	
	%end;
%Mend;
%Eksport;

%Macro Print;

%If &Output. = EXCEL %Then %Do;

	Proc print noobs label data=sashelp.class;

	Run;

	ODS Excel close;

%end;
%Mend;
%Print;

Regards

AMUMOR

Re: Stored Process - output to XLSX

DavidPope — Wed, 31 May 2017 13:35:47 GMT

It might be due to the lack of your encoding = 'UTF-8' on your second _webout

I'm guessing that as a result you may be mixing encodings and you end up writing out characters in your value= statement that don't exist in the "default" encoding.

Re: Stored Process - output to XLSX

AMUMOR_SAS — Wed, 31 May 2017 18:13:44 GMT

Hi, and thank you for your Reply.

Do you mean that I simply write like this:

ODS Excel file = _webout encoding='UTF-8';

Regards

Morten Fredrik

Re: Stored Process - output to XLSX

DavidPope — Wed, 31 May 2017 18:29:05 GMT

No, what I thought would help would be adding the encoding="UTF-8" to your second

data null;

just like the one on your first _webout in your first data null; block.

Re: Stored Process - output to XLSX

AMUMOR_SAS — Wed, 31 May 2017 18:38:49 GMT

Thank's, but it did not help. I get the same message when I try to open the Excel File. Sorry.

Re: Stored Process - output to XLSX

DavidPope — Wed, 31 May 2017 19:05:55 GMT

One other suggestions would be to change the name of your second .xlsx file to not include spaces so Test2.xlsx

Re: Stored Process - output to XLSX

AMUMOR_SAS — Wed, 31 May 2017 19:22:53 GMT

Hello again,

I have solved it. I had a dummy macro at the bottom of my Stp which I deliberately envoked without having the "%" preceding the macro name, just to get a debug list. When I ran the StP to get Excel output, the debug log was placed in the Excel file and caused the error message.

Regards

Morten Fredrik

Re: Stored Process - output to XLSX

DavidPope — Wed, 31 May 2017 19:24:28 GMT

Morten - glad you resolved it and shared the solution on the board too.