Proc Report: relation between width (SAS output) and style(column) (ODSRTF)

[JuanVte]

Hi everybody,

I am trying to have a realistic representation of what I will find in the RTF document compared with the SAS output.

For that, when I create a RTF file, I use Courier New 8pt. This font should be the same that we have in the SAS output.

The first problem is how to set the column width using proc report.

For the SAS output it can be set using option "width". This option sets the number of characters to be used. So if I want to write Peter, I will have enough doing width=5.

This option does not work in ods rtf, so we need the option
style(column)=[cellwidth=xx units]

The problem is that I am not able to relate the number of character in the option width with the number of unit in rtf.

I am trying to do this kind of reasoning:

- One character takes 8 points,
- 72 pt correspond to 1 inch,
- so 1 character takes 8/72 inch,
- so n characters takes n* 8/72 inch.

But it does not work.

I am using the following code:

%let width = 10;

ods rtf file="c:\temp\format.rtf";
proc report missing headline headskip nowd data=sashelp.class split='*';
columns name;
define name / display 'Name' style(column)=[cellwidth=%sysevalf(8/72 * &width)in] width=&width;
run;
ods rtf close;

Any idea??

Thanks!!
Juanvte

[Cynthia_sas]

Hi:
For most SAS installations, the LISTING or OUTPUT window output is shown in SAS Monospace, not Courier New (unless you have changed your system defaults).

However, that is not the issue. You have 2 separate issues:
1) Office/Word (and that includes RTF documents opened in Word) converts all units of measure -- inches, cm, point sizes, etc to a unit of measure called TWIPS -- twentieths of a printer's point, so if you are attempting to second guess Word in font size, it is generally very difficult. Word doesn't just take into account the number of characters in a cell. It also takes into account the amount of space around the characters in a cell, the size of the border needed around the cell. In addition, when you use one of the "big three" ODS destinations -- HTML, RTF and PDF, ODS tries to "fit" output on the page horizontally, by ever so slightly adjusting those items (like cellpadding and cellspacing), particularly for paged destinations (which RTF is). -- This kind of behind-the-scenes adjustment will be VERY hard for you to replicate.

2) The issue of how you set width is partly a MOOT point, because WDTH=, the PROC REPORT option is ignored for most ODS DESTINATIONS, as described here:
http://support.sas.com/kb/2/549.html (WIDTH= is not listed in this TS note, however, it is listed on this page, which shows "workarounds" for how to simulate the options in ODS HTML, specifically:
http://support.sas.com/rnd/base/ods/templateFAQ/repoption.html

So, while your formula attempt was inventive, your WIDTH= option was ignored by ODS RTF. And WIDTH= in LISTING, does to some extent determine how many characters to use, so a value of "SUPERCALIFRAGILISTICEXPEALIDOCIOUS" would normally take 34 characters and in the LISTING window and ONLY in the LISTING window, could you limit the string to display only "SUPER" with WIDTH=5. ODS will NEVER restrict the display of a string based on a WIDTH or CELLWIDTH value.

If, for example, you wanted to limit the number of characters displayed to only 5, you would have to use FORMAT= with PROC REPORT, as shown in the example below.

Note, that Output #1 only restricts the display to SUPER in the LISTING destination. While Output #2, will restrict the display to SUPER in all destinations (including LISTING). Output #3, does NOT restrict the display to only the first xxx characters -- the cell width is set and then the variable value is displayed in that width, with automatic wrapping, if necessary. Also note that because of the conversion to TWIPS, the column size might be different in RTF than it is in PDF (the other paged destination, where horizontal measurement matters).

The bottom line is that setting cellwidth is somewhat of a trial and error proposition because it's almost impossible to completely replicate ODS behind the scene's work -and- the Word conversion to TWIPS. If you are having issues with fitting very wide output onto a horizontal page in PDF or RTF, then you might search previous forum postings for the string "very wide" in order to see my previous posting on this topic -- with other tips on changing cellpadding among other attributes.

cynthia
[pre]
data test;
length wordvar $34;
name = 'aaa';
wordvar = 'supercalifragilisticexpealidocious';
run;

ods listing;
ods rtf file='ts.rtf' ;
ods pdf file='ts.pdf' ;
ods html file='ts.html' style=sasweb;
proc report data=test nowd;
title '1) using width= ONLY works for LISTING';
column name wordvar;
define name / 'name';
define wordvar / 'wordvar' width=5;
run;

proc report data=test nowd;
title '2) using format to set number of chars to display';
column name wordvar;
define name / 'name';
define wordvar / 'wordvar' format=$5.;
run;

proc report data=test nowd;
title '3) using cellwidth to set cell size';
column name wordvar;
define name / 'name';
define wordvar / 'wordvar'
style(column)={cellwidth=.15in};
run;

ods _all_ close;
[/pre]

[JuanVte]

Thanks Cynthia,

It is a pity because it is exhausting to work with this independence. Open/close and open/close the Word processor, move again and again the sentence ods rtf file=... and ods rtf close. "Opps, I forgot to close Word and it did not work. Run it again!" And finally, forget the listing output because it is useless.

To report with ODS becomes a challenge: nice output with an important cost of time. From my point of view, it is very difficult to understand that we need to play a trial and error game.

What do you recommend to save time? Any idea to make the things easier? new SAS versions? I am using 9.1.3! any application that I do not know? Any usefulness macro to report?

Thanks for your help and patience!
Juanvte.

[Cynthia_sas]

Hi:
Sadly, the fact that SAS cannot overwrite an open Word document is an issue for Microsoft Word. Once Word (or Excel) holds the file open for write access, SAS is not allowed to write over that file name. Your only choices, as you note, are to 1) remember to close Word instinctively (this will come over time) or
2) change the ODS RTF file name while you are testing and then once in production, use the "final" name. But applications have to play nice with write access and SAS only can create a file name or write over a file name if no other application is holding the file open for WRITE access. Since Word grabs the access rights as soon as the file is opened, that prevents SAS from writing over the same name a second time until Word lets go of the document.

You did not say whether you were using WIDTH= to limit the number of characters (as in display only SUPER for SUPERCALIFRAGILISTICEXPEALIDOCIOUS) and whether you wanted CELLWIDTH= to duplicate this behavior. Since CELLWIDTH will NOT duplicate this behavior -- that part should not be exhausting or need any trial and error. Use format=$5. instead of width=5 and you will see SUPER in all destinations, including ODS RTF.

I really don't understand your use of cellwidth or why you feel the need to "pre-control" the width that ODS is going to use (if it's not the above example).

Is it that you want a cellwidth to ALWAYS be the same size, no matter what the data looks like???? So for example, you would want the NAME column to always be a certain size, no matter whether you had names of "Al", "Pete" and "Bob" or names of "Frederick", "Aloysius", "Fibonnaci", 'Salvatore" or "Jean-Baptiste"????

That would seem to bypass how ODS operates. By default, if you have data with all "short" names, then ODS would size the column to fit the short names, if possible and if you have data with all "long" names, then ODS would size the column to fit the long names. I consider this as desirable behavior (that ODS tries to accomodate the cellwidth to the actual data).

You can specify cellwidths in PT, CM, EM, IN or Percents. You can also tell SAS to stretch the outputwidth of the WHOLE table. I rarely use cellwidth to alter width size for a particular cell, unless I have something like a COMMENT variable with a LOT of text and I want to make the cellwidth smaller so the COMMENT wraps.

Perhaps if you can explain in a bit more detail the -initial- problem that ended in your idea to use cellwidth as the solution.... there would be more suggestions.

cynthia

[blueskyxyz]

@Cynthia_sas , it is also my question.

for example,  list a variable txt='You are a sas programmer.' in rtf listing, define the cellwidth=1 in, the output may like as below:

You are a

sas programmer

/*have two lines*/

how to count the lines for a variable based on the cellwidth and length?

If I got the number lines, then I can know how many lines in each page.

[Cynthia_sas]

Hi:
This original posting is from 2010. I answered your questions somewhat in your other posting about lines. Basically, you can't know, ahead of time, where the data will wrap to a new line in RTF, given a font and cellwidth. So you either need to calculate your own page break variable that allows for every row on the page to have 2 lines or something like that and then that will end up leaving a lot of white space at the bottom of the page, if all the rows have 1 line. It's not really something you can calculate.

If you want to refer back to a 10 year old posting, it's better to start a new post and just put a link back to the older/original post.
Cynthia

[blueskyxyz]

@JuanVte ， I also met this question, how to calculate the lines for different variables in one column with certain width(cellwidth= xx%/ xx in )? to output the listing with more obs.