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
One other suggestions would be to change the name of your second .xlsx file to not include spaces so Test2.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.
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
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.
Thank's, but it did not help. I get the same message when I try to open the Excel File. Sorry.
One other suggestions would be to change the name of your second .xlsx file to not include spaces so Test2.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
Morten - glad you resolved it and shared the solution on the board too.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.