The SAS Output Delivery System and reporting techniques

Dealing with wide tables, part 2.

Reply
Occasional Contributor
Posts: 11

Dealing with wide tables, part 2.

All,

Cynthia@SAS Already posted some useful threads about fitting wide tables on a page, when using the RTF or PDF output.
She recommended a few adjustments in the PROC REPORT:

style(report)={font_face='Arial Narrow' font_size=6pt cellpadding=2px}
style(header)={font_face='Arial Narrow' font_size=6pt}
style(column)={font_face='Arial Narrow' font_size=6pt}

Using a narrow font and a small fontsize will do the trick for me.
But I have like 40 different programs with a PROC REPORT in them and I don't want go through all them. There must be another way, but wich one?

It has probably something to do with a PROC TEMPLATE...

Greetings, Raoul.
SAS Super FREQ
Posts: 8,868

Re: Dealing with wide tables, part 2.

Hi:
Yes, the answer does have something to do with PROC TEMPLATE. This is exactly the reason you would now move your style information into a STYLE template for use with RTF or PDF file creation.

To change the fonts, you need to change the FONTS style element in the style template, as described in these Tech Support notes. You may need to create one template for PDF and a slightly different template for RTF. That's because by default, PDF uses the STYLES.PRINTER template and RTF, by default, uses the STYLES.RTF template. So they may need to have different "parent templates" for each destination (as shown in these notes):
http://support.sas.com/kb/24/217.html
http://support.sas.com/kb/24/489.html
http://support.sas.com/kb/23/368.html

Then, once you have your style template, you would invoke it:[pre]
ods rtf file='usenew.rtf' style=myrtftemp;
ods pdf file='usenew.pdf' style=mypdftemp;[/pre]

Note that the above notes only discuss changing font. If you also need to change the cellpadding, then you will probably need to search for cellpadding and change either the TABLE element or the OUTPUT element in order to specify a cellpadding of 2px.

For general Template FAQ and a useful paper:
http://support.sas.com/rnd/base/ods/templateFAQ/index.html
http://www2.sas.com/proceedings/sugi28/195-28.pdf

cynthia
Occasional Contributor
Posts: 11

Re: Dealing with wide tables, part 2.

Cynthia,

Thanks for your answer, which is very helpful. I do have another question.

I've made a Stored Process which displays a HTML-page using the _webout filref. The HTML-page contains some inputfields for the enduser and one of those inputfields is a combobox, that contains the output destinations. I've defined a few (HTML, PDF, PDF Narrow, Micorsoft Excel, Microsoft Word, Microsoft Word Narrow). The "narrow-destinations" use a different template.
When the user clicks the 'show report' button, another stored process is invoked which contains the code for the report and uses the parameters entered by the enduser.

Another way is to use the default parameter-functionality embedded in a stored process, that is defined with EG and is used in both EG and Office and Web Report Studio.
So I have two ways in presenting parameters to the enduser. First is the webpage, second is the default parameter-functionality embedded in a stored process.

Now, here's the problem. When the report is running, a macro is used to control the ODS. But the different clients act differently when i choose a particular output. For example: when I use the web-interface and i choose to generate a Microsoft Word Narrow output, Word is opened and the document is displayed.
But when i use the Add-in, the result is an XML-document with the root-element "SASReport". I think this is the default output for WRS.

How can I fix this problem?

Raoul.

P.S. The code I use in the macor to control the ODS is like the following:

%MACRO ods_open(formaat = );
%GLOBAL _ODSDEST _ODSSTYLE _ODSSTYLESHEET _ODSOPTIONS;
OPTIONS ORIENTATION = LANDSCAPE;

/* Create some templates ... */
PROC TEMPLATE;
...
RUN;

ODS PATH RESET;
/* Add the templates to the searchpath */
ODS PATH (PREPEND) work.templat(UPDATE);

%LET formaat = %UPCASE(&formaat);
%IF &formaat = MICROSOFT EXCEL %THEN %DO;
%LET _ODSDEST = ;
%LET _ODSSTYLE = ;
%LET _ODSSTYLESHEET = ;
%LET _ODSOPTIONS = ;
DATA _NULL_;
rc = STPSRV_HEADER('Content-type', 'application/vnd.ms-excel');
rc = STPSRV_HEADER('Content-disposition', 'attachment; filename=tmp.xls');
RUN;
%END;
%ELSE %IF &formaat = MICROSOFT POWERPOINT %THEN %DO;
%LET _ODSDEST = ;
%LET _ODSSTYLE = ;
%LET _ODSSTYLESHEET = ;
%LET _ODSOPTIONS = ;
DATA _NULL_;
rc = STPSRV_HEADER('Content-type', 'application/vnd.ms-powerpoint');
rc = STPSRV_HEADER('Content-disposition', 'attachment; filename=tmp.ppt');
RUN;
%END;
%ELSE %IF &formaat = MICROSOFT WORD %THEN %DO;
%LET _ODSDEST = rtf;
%LET _ODSSTYLE = styles.rtftemp;
%LET _ODSSTYLESHEET = ;
%LET _ODSOPTIONS = ;
DATA _NULL_;
rc = STPSRV_HEADER('Content-type', 'application/msword');
rc = STPSRV_HEADER('Content-disposition', 'attachment; filename=tmp.doc');
RUN;
%END;
%ELSE %IF &formaat = MICROSOFT WORD NARROW %THEN %DO;
%LET _ODSDEST = rtf;
%LET _ODSSTYLE = styles.rtfnarrow;
%LET _ODSSTYLESHEET = ;
%LET _ODSOPTIONS = ;
DATA _NULL_;
rc = STPSRV_HEADER('Content-type', 'application/msword');
rc = STPSRV_HEADER('Content-disposition', 'attachment; filename=tmp.doc');
RUN;
%END;
%ELSE %IF &formaat = PDF %THEN %DO;
%LET _ODSDEST = pdf;
%LET _ODSSTYLE = styles.pdftemp;
%LET _ODSSTYLESHEET = ;
%LET _ODSOPTIONS = BOOKMARKGEN=NO COMPRESS=9 STARTPAGE=NO;
DATA _NULL_;
rc = STPSRV_HEADER('Content-type', 'application/pdf');
rc = STPSRV_HEADER('Content-disposition', 'attachment; filename=tmp.pdf');
RUN;
%END;
%ELSE %IF &formaat = PDF NARROW %THEN %DO;
%LET _ODSDEST = pdf;
%LET _ODSSTYLE = styles.pdfnarrow;
%LET _ODSSTYLESHEET = ;
%LET _ODSOPTIONS = BOOKMARKGEN=NO COMPRESS=9 STARTPAGE=NO;
DATA _NULL_;
rc = STPSRV_HEADER('Content-type', 'application/pdf');
rc = STPSRV_HEADER('Content-disposition', 'attachment; filename=tmp.pdf');
RUN;
%END;
%ELSE %IF &formaat = HTML %THEN %DO;
%LET _ODSOPTIONS = RS=NONE;
/************************************************************************************/
/* Code from: http://support.sas.com/kb/30/783.html */
/************************************************************************************/
ODS PATH (prepend) work.templat(update);
PROC TEMPLATE;
DEFINE TAGSET tagsets.nostyle;
PARENT = tagsets.html4;
embedded_stylesheet = no;
END;
RUN;
ODS HTML BODY = _webout stylesheet=(URL="http://.../stylesheets/mystylesheet.css");

ODS HTML CLOSE;

%LET _ODSDEST = tagsets.nostyle;
%END;
%ELSE %DO;
%LET _ODSDEST = html;
%LET _ODSSTYLE = sasweb;
%LET _ODSSTYLESHEET = ;
%END;

%stpbegin;

%MEND ods_open;

%MACRO ods_close;
%stpend;
%MEND ods_close;
SAS Super FREQ
Posts: 8,868

Re: Dealing with wide tables, part 2.

Hi:
Sadly, the STPSRV_HEADER method only works with web-based applications. Since you used one of the Tech Support notes as the basis for part of your code, you might wish to work with Tech Support on a SP that will work in the SAS Add-In for Microsoft Office and takes into account the fact that you can only send certain types of markup to the Add-In clients. I looked at the Tech Support note that you referenced and although it was hard to find, the description of the problem was that the SP was running in a web-based scenario:
"The initial symptom of this problem is that a style sheet specification will be ignored if both of the following circumstances are in place:
a) the _ODSSTYLESHEET option is used with the %STPBEGIN/%STPEND macros OR an ODS HTML with the STYLESHEET= option is included in a stored process;
AND
b) the stored process is being executed from a Web browser via the Stored Process Web Application (including the Portal).
"

There was nothing in the note which implied this technique (tagsets.nostyle) would work with the Add-In client applications. I actually see several issues with your code:
1) tagsets.nostyle is creating HTML-based markup -- not all client apps can "receive" HTML. You'll have the same issue if you change _ODSDEST to be HTML -- not all client apps receive HTML results
2) your SP uses or tries to use STPSRV_HEADER to control the HTTP protocol content-type header for RTF, PDF, etc -- but only web-based methods use the HTTP protocol and STPSRV_HEADER
3) your stylesheet workaround for HTML may only work in web-based applications

Not all the client apps in the BI platform are using the HTTP protocol to receive the SP results. Every client application -- EG, Add-In for Word, Add-In for Excel, etc, has an options dialogue box that can be set by each user. That dialogue box has a Results Tab and the Results Tab shows you which types of output can be "received" by that client application.

Web Report Studio can ONLY receive SAS Report XML (not HTML, not RTF)
EG can receive SAS Report XML, HTML, RTF, PDF
Excel can receive SAS Report XML, HTML and CSV (no RTF or PDF)
Word can receive SAS Report XML , HTML and RTF (no CSV or PDF)
PPT can ONLY receive SAS Report XML (not HTML)

So, for example, any SP that runs in PPT can ONLY receive SAS Report XML -- that's because part of the Add-In's internal code for PPT takes the SASReport XML and transforms the XML so PPT can render the SP results.

For another example, it would not be appropriate for Excel receive or open a PDF file -- so that's why PDF results from an SP are not a choice for Excel with the Add-in.

So it's entirely likely that your custom tagset template will be completely ignored by many of the client applications -- OR that the behind the scenes process will actually change your _ODSDEST to be SASReport XML. It's also possible that some of the client applications will not respect your mystylesheet.css file. Web Report Studio, for example, does not use stylesheets or SAS style templates. There is a way to embed/define CSS references in the XML that runs WRS -- but WRS, would not use your stylesheet, as specified in the SP. (I'm not sure how the other client apps would react to your stylesheet reference -- that's a question for Tech Support.)

Your SP will work best when invoked from the web (like the Portal or using the Stored Process Web App) -- it will probably not work the way you want in most of the platform's client applications, like the Add-in.

cynthia
Occasional Contributor
Posts: 11

Re: Dealing with wide tables, part 2.

Cythia,

Many thanks for your vast and instructive answer. I already expected to make the difference between (a) the webapplication and (b) the SAS-clients. About the STPSRV_HEADER: of course that only applies to webclients. I'm such a fool!

I started with invoking the SP in a web-based client and all of the outputformats (PDF, Excel, etc) were generated just fine.
Then I ran into these problems using the same code when using the Add-in.
I'll look into the options dialog boxes in the SAS-clients to see how the user can control the type of results.

I do have a final (short) question.
Is it possible to check which type of client (web-based versus SAS-clients) invoked the SP?
Maybe there is a global macrovariable created by a web-based client that is not created when the SP is invoked by a SAS-client? Or vice-versa?

Many regards,
Raoul.
SAS Super FREQ
Posts: 8,868

Re: Dealing with wide tables, part 2.

Hi:
In SAS 9.1.3, as far as I know, there is no automatic parameter to test for which client application is being used to request a SP.

However, if you think about it, every person who's requesting an SP knows WHICH application THEY are using for the request. Although you could make a parameter and have each person tell you explicitly what client app they're using, they may not like this approach. So here's another approach.

Let's say I had the SHOE macro program stored in an autocall macro library and I had my SETUP macro program also stored in an autocall macro library (on my server). I'd be tempted to make several different SPs:

1) Shoe_Word
[pre]
%global width_choice thisclient ... other parms ...;
%let thisclient = Word;
%setup;
%stpbegin;
%shoe;
%stpend;
[/pre]

2) Shoe_EG
[pre]
%global width_choice thisclient ... other parms ...;
%let thisclient = EG;
%setup;
%stpbegin;
%shoe;
%stpend;
[/pre]

3) Shoe_PPT
[pre]
%global width_choice thisclient ... other parms ...;
%let thisclient = PPT;
%setup;
%stpbegin;
%shoe;
%stpend;
[/pre]


4) Shoe_Excel
[pre]
%global width_choice thisclient ... other parms ...;
%let thisclient = Excel;
%setup;
%stpbegin;
%shoe;
%stpend;
[/pre]

If the end-users submit the Shoe_Excel stored process from within PPT, then it won't work for them, but then at least, they might think (when it didn't work) Oh, wait, didn't he say to use the Shoe_PPT stored process from within PPT and the Shoe_Excel stored process from within Excel. (Or, I'd hope that the name of the SP would be enough of a clue).

In this scenario, although you do have a few MORE SPs, you can set your own macro variable for the client app in each SP and you could invoke the same %SETUP macro program and the %SHOE macro program for each SP.

On the other hand, you could just make a parameter for the user to select and make them tell you what client app they're using. It really depends on your users -- you know them, what are they more likely to want to do.

cynthia
Ask a Question
Discussion stats
  • 5 replies
  • 321 views
  • 0 likes
  • 2 in conversation