BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
AMUMOR_SAS
Obsidian | Level 7

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

1 ACCEPTED SOLUTION

Accepted Solutions
DavidPope
SAS Employee

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

7 REPLIES 7
DavidPope
SAS Employee

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.

AMUMOR_SAS
Obsidian | Level 7

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

DavidPope
SAS Employee

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.

     

AMUMOR_SAS
Obsidian | Level 7

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

DavidPope
SAS Employee

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

AMUMOR_SAS
Obsidian | Level 7

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

DavidPope
SAS Employee

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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