Want to create macro variable

[kumarsandip975]

Hi Everyone, I have below sample program, which basically download the xlsx file on webbrowser via sasweb(sas/intrnet).

** Test 2: create xlxs file;
ods excel file=_webout style=plateau;
data _null_;
rc=appsrv_header('Content-type','application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
rc=appsrv_header('Content-disposition','attachment; filename=test2.xlsx');
run;
proc print noobs label data=sashelp.class;
run;
ods excel close;

I need to use this sample in mainframe dataset having 72-character length dataset as default. Since as above rc= value is more than 72 characters, so I thought to use into variable by defining into %let,but looks like macro variable is not resolving currectly.
Can someone help me, what I am doing wrong. Error message is atatched.

options symbolgen;
%let string1="appsrv_header('Content-type','application/vnd.openxml";
%let string2="formats-officedocument.spreadsheetml.sheet')";
%let string3=appsrv_header('Content-disposition','attachment;;
%let string4=filename=test2.xlsx');
ods excel file=_webout style=plateau;
data _null_;
rc=&string1.&string2.;
rc=&string3.&string4.;
run;
proc print noobs label data=sashelp.class;
run;
ods excel close;

Error message:

33  +options symbolgen;
34  +%let string1="appsrv_header('Content-type','application/vnd.openxml";
35  +%let string2="formats-officedocument.spreadsheetml.sheet')";
36  +%let string3=appsrv_header('Content-disposition','attachment;;
37  +%let string4=filename=test2.xlsx');
38  +ods excel file=_webout style=plateau;
39  +data _null_;
40  +rc=&string1.&string2.;
SYMBOLGEN:  Macro variable STRING1 resolves to "appsrv_header('Content-type','application/vnd.openxml"
SYMBOLGEN:  Macro variable STRING2 resolves to "formats-officedocument.spreadsheetml.sheet')"
41  +rc=&string3.&string4.;
SYMBOLGEN:  Macro variable STRING3 resolves to appsrv_header('Content-disposition','attachment;;%let string4=filename=test2.xlsx')
SYMBOLGEN:  Unable to resolve the macro variable reference &string4
41  +rc=&string3.&string4.;
                                                                                          The SAS System

                  --------
                  22
ERROR 22-322: Syntax error, expecting one of the following: a name, a quoted string, a numeric constant, a datetime constant, a missing value, arrayname, (, +, -, INPUT, NOT, PUT, ^, _NEW_, ~.  

41  +rc=&string3.&string4.;
                  --------
                  201
ERROR 201-322: The option is not recognized and will be ignored.

WARNING: Apparent symbolic reference STRING4 not resolved.

[kumarsandip975]

even I used "" in string3 , and string4, but same issues.

[PaigeMiller]

I understand that the server has a 72 char limit, but does that really mean the value of rc= has to be 72 chars or less? Does that really mean that the macro variables have to be 72 chars or less?

What happens if you don't split rc or the macro variables? Doesn't the whole thing work without splitting rc= and doesn't the whole thing work without macro variables? If you split the variables to 72 characters and then combine them, you get something bigger than 72 characters.

Convince me that macro variables are the way to go here.

If you really want to use macro variables, the special characters (comma, slash, hyphen, semi-colon and mismatched quotes) must be enclosed in the %STR() function, otherwise the macro processor interprets them as an operator and not as something in a text string

%let string1=%str(appsrv_header('Content-type','application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'));

%let string2=%str(appsrv_header('Content-disposition','attachment; filename=test2.xlsx'));

[Quentin]

SAS doesn't mind line breaks, so seems like you could do it like:

rc=appsrv_header('Content-type',
'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
rc=appsrv_header('Content-disposition',
'attachment; filename=test2.xlsx');

Or maybe slightly more readable:

rc=appsrv_header(
  'Content-type'
  ,'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'
);
rc=appsrv_header(
  'Content-disposition'
  ,'attachment; filename=test2.xlsx'
);

[Tom]

What DATASET?  Are you talking about a SAS dataset?  Your code does not appear to be using any SAS dataset.

Are you perhaps using the ancient IBM Mainframe dialect where DATA SET is used to refer to any FILE? If so what FILE are you talking about that has 72 byte records?  Is it the SAS program file?  If so then SAS does not care if you use multiple lines of code to issue one statement.

Put some line breaks between the tokens in your code

Or even break the strings into shorter strings.

[Kurt_Bremser · Accepted solution]

SAS datasets on mainframes follow the same basic limits as on other platforms, so the maximum length of strings is 32767.

If your issue is with SAS program files OTOH,  you can split function calls over code lines:

data _null_;
rc = appsrv_header(
  'Content-type',
  'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet'
);
rc = appsrv_header(
  'Content-disposition',
  'attachment; filename=test2.xlsx');
run;

[kumarsandip975]

Thanks a lot @Kurt_Bremser  and everyone who help me here.

Yes, it worked fine with this solution on mainframe. Initially, I thought issue with this character length, but now looks like issue with xlsx format on mainframe platform. Same code is working fine on windows.

I have tested following on windows + mainframe (as we have SAS/IntrNet setp on both these platform), every case is working fine on windows, but issue with xlsx on mainframe, not sure why? With your experience, can somebody further suggest about these issues.

** Test 1: create xlx file- working fine on windows as well as on mainframe;
data _null_;
rc = appsrv_header('Content-type','application/vnd.ms-excel;');
rc = appsrv_header('Content-disposition','attachment; filename=test1.xls');
run;
ods listing close;
ods tagsets.excelxp body=_webout style=sasweb;
proc print data=sashelp.class noobs;
title 'test1 excelxp mimetype=application/vnd.ms-excel';
run;
ods tagsets.excelxp close;

** Test 2: create xlsx file- working fine on windows but on mainframe;
ods excel file=_webout style=plateau;
data _null_;
rc=appsrv_header('Content-type','application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
rc=appsrv_header('Content-disposition','attachment; filename=test2.xlsx');
run;
proc print noobs label data=sashelp.class;
run;
ods excel close;

Error from mainframe with xlsx:

** Test 3: create pdf file - - working fine on windows as well as on mainframe;
ods pdf file=_webout;
data _null_;
rc = appsrv_header('Content-type','application/pdf');
rc = appsrv_header('Content-disposition','attachment; filename=test3.pdf');
run;
proc print noobs label data=sashelp.class;
run;
ods pdf close;

[Tom]

Why are you creating an XML file on mainframe instead of an XLSX file?

Are you running an old version of SAS?

Can you get access to a more current version that supports ODS EXCEL?

[kumarsandip975]

Hey @Tom As I mentioned in test2, I am testing for xlsx , not XML.

[Kurt_Bremser]

---

@kumarsandip975 wrote:

---

This does not look like a SAS log message; where and when do you get that?

[Quentin]

Wow, I had assumed that SAS/IntrNet was deprecated, but looks like it's still being developed, and SAS IntrNet 9.4M8 shipped in January 2023.  Are you using a current release?

As a first step, I would follow the suggestion of the error message and set _debug=131, so that SAS IntrNet will return the SAS log to the browser.  I think you would just add &_debug=131 to the URL.  An alternative is to use PROC PRINTTO to write the SAS log to a file.  Bottom line, you need to find a way to see the SAS log from a step that fails.  It could be that ODS EXCEL is failing, or the style in unknown, or ...

I work on a SAS 9 BI server, using SAS Stored Processes for web reports, so it's similar to SAS IntrNet.  My general approach is to separate report production from the step of streaming the results to the browser. So you could do it something like (untested):

ods excel file="%sysfunc(pathname(work))/myreport.xlsx" style=plateau;
proc print noobs label data=sashelp.class;
run;
ods excel close;


*Binary copy code from http://support.sas.com/kb/6/588.html now  dead link; 
data _null_;
    file _webout recfm=s;
    infile "%sysfunc(pathname(work))/myreport.xlsx" recfm=n;
    if _n_ = 1 then do;
      rc=appsrv_header('Content-type','application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
      rc=appsrv_header('Content-disposition','attachment; filename=myreport.xlsx');

    end;
    input c $char1.;
    put c $char1. @@;
run;

[kumarsandip975]

@Quentin Thanks for understanding. Here is complete log, it seems to me issue with xlsx , it is not able to recognize _webout. Can you please help identifying issue. 

ods excel close;
Ther relevant part of the log is this:
NOTE: running request program qz4prog.balans2.sas
NOTE: %INCLUDE (level 1) file SANPROG(TEST2.sas) is file SANKE000.TDXP.SASPGM(TEST2).
2 +ods excel file=_webout style=plateau;
3 +data _null_;
4 +rc=appsrv_header
5 +('Content-type',
6 +'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
7 +rc=appsrv_header('Content-disposition','attachment;
8 +filename=test2.xlsx');
9 +run;
NOTE: The DATA statement used 0.00 CPU seconds and 46010K.
NOTE: The address space has used a maximum of 1940K below the line and 63320K above the line.
10 +
11 +proc print noobs label data=sashelp.class;
12 +run;
NOTE: There were 19 observations read from the data set SASHELP.CLASS.
NOTE: The PROCEDURE PRINT printed page 1.
NOTE: The PROCEDURE PRINT used 0.07 CPU seconds and 49978K.
NOTE: The address space has used a maximum of 1940K below the line and 63320K above the line.
13 +ods excel close;
ERROR: Invalid physical name.
NOTE: %INCLUDE (level 1) ending.
NOTE: request has completed
WARNING: Application generated no output.
Content-type: text/html Cache-Control: no-cache
--------------------------------------------------------------------------------
This request completed with errors.

[Quentin]

Interesting, thanks for the log.

I agree, it looks like a problem with ods EXCEL not liking file=_webout, which is surprising.  (unless the problem is the plateau style, may want to remove that, just for simplicity).

Did you try separating the generation of the excel report from the step sending the report to _webout, as in the mock example I showed?  If there is a problem with ODS EXCEL writing to _webout that would avoid it. Basically, you use ODS EXCEL to write the .XLSX file somewhere on the server (can be in work, or even a permanent location).  And then you use a data _null_ step to send a binary copy of the file to _webout.  

This is how I do all of my stored process reporting, because after generating the report, I scan the SAS log myself to look for errors/warnings/bad notes before sending the results back.

And of course, if you don't get other good ideas here, you can always call tech support.  They're terrific.

[kumarsandip975]

@Quentin thanks for further suggestions, yes I tried removing style plateau option, but same issues. Second test , that you suggested, Creating first xlsx without _webout, but I am not able to test it on mainframe, because we can't create xlsx temporarily or permanently on mainframe, and it gives us error "PHYSICAL FILE NOT FOUND". I am really stuck at this moment, will go to Tech Support. 

[Tom]

Ok. 

So the issue is you can create XML files directly to _WEBOUT using the ODS TAGSETS.  But you cannot create an XLSX file directly to _WEBOUT using ODS EXCEL.  Make some sense because making an XLSX file probably requires rewriting parts of it along the way.

The solution is to create the file first and then copy the final XLSX file to the _WEBOUT fileref.

Perhaps something like:

filename xlsx temp recfm=n;
ods excel file="%sysfunc(pathname(xlsx))";
proc print data=sashelp.class;
run;
ods excel close;
%let rc=%sysfunc(fcopy(xlsx,_webout));