SAS Office Analytics, SAS Add-In for Microsoft Office, and other integrations

Preserve table width i Word Add-in (or set table size in the code)

Reply
Occasional Contributor
Posts: 9

Preserve table width i Word Add-in (or set table size in the code)

I have a Wordreport with 3 tables on a page. The end user want the 3 tables to have the same width, but when the tables are refreshed they go back to fit then columns.

In excel Add-in you can check for "Preserve column width" how do you do something similar i Word Add-in?

I've tried to contact the danish SAS-support who says the width options i proc report does not work in Add-in.
SAS Super FREQ
Posts: 8,646

Re: Preserve table width i Word Add-in (or set table size in the code)

Hi:
The WIDTH option inside PROC REPORT:
[pre]
define charvar / display width=10 'CharVar';
[/pre]

is a LISTING only option and is generally ignored by any destination other than LISTING. This is also true of other PROC REPORT options, such as: HEADLINE, HEADSKIP, DOL, DUL, OL, UL, SKIP, FLOW, SPACE and PANELS.

If you have a Stored Process that is returning results to Microsoft Word via the SAS Add-in for Microsoft Office, as part of the SAS Enterprise Intelligence Platform (BI Platform). Then some of the ODS STYLE= options may work for you to set the table width. For example, if you wanted the WHOLE TABLE to be 100% of the available area, then you would use this code for HTML, RTF and PDF results:
[pre]
proc report data=mylib.mytable nowd
style(report)={outputwidth=100%};
[/pre]

For example, if I run this code in Base SAS (not as a stored process), then all the output tables are the same size in HTML, RTF and PDF:
[pre]
ods rtf file='c:\temp\outwidth.rtf' startpage=no;
ods pdf file='c:\temp\outwidth.pdf' startpage=no;
ods html file='c:\temp\outwidth.html' style=sasweb;

proc report data=sashelp.class(obs=2) nowd
style(report)={outputwidth=80%};
column name age height;
run;

proc report data=sashelp.shoes(obs=2) nowd
style(report)={outputwidth=80%};
column region product sales;
run;

proc report data=sashelp.prdsale(obs=2) nowd
style(report)={outputwidth=80%};
column region country actual;
run;
ods _all_ close;
[/pre]

In addition to the OUTPUTWIDTH style attribute, which is available for the whole table; it is also possible to alter individual cellwidths on the DEFINE statement by using the CELLWIDTH style attribute.

Note that In SAS 9.1.3, the attributes were OUTPUTWIDTH and CELLWIDTH; in SAS 9.2, the attributes were simplified to just WIDTH. However, the older syntax attribute names still work for compatibility. so these are equivalent statements:
[pre]
proc report data=sashelp.class(obs=2) nowd
style(report)={outputwidth=80%};

proc report data=sashelp.class(obs=2) nowd
style(report)={width=80%};

define charvar / display style(column)={cellwidth=1in};

define charvar / display style(column)={width=1in};
[/pre]

There IS a possibility, however, that your ODS style overrides may only work if the Stored Process result type is set to HTML or RTF. Sometimes, the default result type for Stored Processes in the Add-in is SASReport XML -- there is a chance that the ODS style overrides will not work for that result type. This is an instance where you might need to work with the person who developed the Stored Process to determine whether or not they want to change the result type (&_ODSDEST) to HTML or RTF specifically for this Stored Process.

Keep in mind that not all client applications can read RTF result types from a Stored Process. If you are going to want the most flexible Stored Process, the result type should be HTML, because most client applications can read this result type. (The only 2 client applications that CANNOT process HTML results from a Stored Process are SAS Web Report Studio and Microsoft PowerPoint -through the Add-in. These 2 client apps can ONLY receive SASReport XML results from a Stored Process.)

cynthia
Occasional Contributor
Posts: 9

Re: Preserve table width i Word Add-in (or set table size in the code)

Hi Cynthia

Thanx alot for the quick answer - I use List Data in my Wordrepport.
But you answer with the ODS style did it.

It now works with ODS style= HTML and style(report)={outputwidth=80%}

Martin
Occasional Contributor
Posts: 9

Re: Preserve table width i Word Add-in (or set table size in the code)

Hi Cynthia

After I changed the ODS to HTML a new problem shown up;(


I have a title over a table (In the above code called title1), this text is the same as previously, but now it shown up as a headline in Word.

Is it possible to keep the text(title) but dont make Word think it is a headline.



proc sql noprint; select put(dato,NLDATEMN.) ,put(intnx('MONTH',dato,-1),NLDATEMN.), put(dato,eurdfde9.),put(year(dato),4.-l)
into: month ,:last_month,: from_date,:year
from WORK._temp_portefoelje_cm;
quit;

title1 j=left h=4.0 "Realkredit Porteføljeændring - &from_date. ";

%symdel from_date;

PROC REPORT DATA=sorttemptablesorted
NOWINDOWS missing SPLIT='*'
style(report)=[just=left width=98%];

COL konto ("&month." rea_dm bud_dm afv_dm);
COL ("&last_month." rea_fm bud_fm afv_fm);
COL ("ÅTD(&year.)" rea_aar bud_aar afv_aar);

define konto /display 'Beløb i t.kr.';
define rea_dm / display 'Realiseret';
define bud_dm / display 'Budget';
define afv_dm/ display 'Afvigelse' ;
define rea_fm / display 'Realiseret';
define bud_fm / display 'Budget';
define afv_fm/ display 'Afvigelse' ;
define rea_aar / display 'Realiseret';
define bud_aar / display 'Budget';
define afv_aar/ display 'Afvigelse' ;
run;

proc sql;
delete * from work.sorttemptablesorted;
quit;
SAS Super FREQ
Posts: 8,646

Re: Preserve table width i Word Add-in (or set table size in the code)

Hi:
I'm not sure what you mean by a "headline". If you mean that the SAS title is going into the Header area of a Microsoft Word document, then that is the expected behavior. A SAS Footnote will go into the Footer area of a Microsoft Word document.

If you use ODS RTF, instead of ODS HTML, then there is an optiion to put the SAS title into the BODY of the Word document, instead of the Header area. However, the downside of this approach is that the title then appears only on page 1 and the footnote only appears on the last page of the ODS RTF document, when opened in Word.

If you have SAS 9.2 and you use ODS TAGSETS.RTF (instead of traditional ODS RTF), then the SAS title does not go automatically into the Header area of the Word document.

However, if you are using the SAS Add-in for Microsoft Office, when you change the stored process results to RTF, I believe it is traditional RTF that is used. In this case, you or your stored process developer would need to supply the BODYTITLE option to make the SAS titles appear in the BODY of the Word document.

You might want to work with Tech Support on this issue, since your question involves the SAS Add-in for Microsoft Office and you will want to make sure that your code runs in multiple clients on the SAS Enterprise Intelligence Platform.

cynthia
Post a Question
Discussion Stats
  • 4 replies
  • 224 views
  • 0 likes
  • 2 in conversation