Your SAS programs, embedded in web apps and elsewhere

Stored Process - output to XLSX

Accepted Solution Solved
Reply
Contributor
Posts: 22
Accepted Solution

Stored Process - output to XLSX

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


Accepted Solutions
Solution
‎05-31-2017 03:23 PM
SAS Employee
Posts: 18

Re: Stored Process - output to XLSX

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

View solution in original post


All Replies
SAS Employee
Posts: 18

Re: Stored Process - output to XLSX

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.

Contributor
Posts: 22

Re: Stored Process - output to XLSX

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

SAS Employee
Posts: 18

Re: Stored Process - output to XLSX

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.

     

Contributor
Posts: 22

Re: Stored Process - output to XLSX

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

Solution
‎05-31-2017 03:23 PM
SAS Employee
Posts: 18

Re: Stored Process - output to XLSX

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

Contributor
Posts: 22

Re: Stored Process - output to XLSX

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

SAS Employee
Posts: 18

Re: Stored Process - output to XLSX

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

☑ This topic is SOLVED.

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

Discussion stats
  • 7 replies
  • 278 views
  • 4 likes
  • 2 in conversation