Fancy Printing - ODS->html->MS Office

[last updated: 14 May 2008]

Introduction

I will make a prediction about ascii reports as of May 2008 and that is that this form of reporting will have been mostly phased out by the end of 2009. At the current time, ascii reports are largely converted to rtf - usually by a macro. You might get a better looking title and a "Page x of Y" label as a result but it still does not look too good. You also get "in-text tables" that are produced seperately that are incorporated into a study report but these tend to look very different from the rtf-style converted ascii tables. Some people have trouble creating these in-text tables. There are various ways of doing it. What I have been doing with my reporting macros %unistats and %unicatrep is to allow them to produce reports that look like "fancy printer" reports suitable to both be printed as they are and also to be incorporated as "in-text tables" into study reports. For me, it is better to have one method for achieving both as greater consistency can be achieved.

ODS->html->Excel

You can use ODS to produce html output using "ODS html". If you know what you are doing, you can get this html output to look like "fancy printer" output, which is to say that the output looks better than plain ascii output even though it is just black and white reports. What most people don't know is that html can be read in by a number of spreadsheets. This is because html output of this sort is also tabular output and it is easy for spreadsheets to convert them to their own internal format. If you read in an html table using Excel, you then have the choice of incorporating it into a Word document, using copy and paste, and voilà - you have your in-text table! Alternatively, if you wanted to, you could edit it and even save it as an html file that a browser like Internet Explorer could read. Html and Excel spreadsheets have crossed into each other's territory. You can use "ODS html" to create a file with the Excel extension .xls . It won't really be an Excel spreadsheet, it will be html, because that is what "ODS html" gives you, but Excel will accept it as a spreadsheet file.

Avoiding the Excel trap

There is something annoying that Excel does that complicates the process of reading in html tables. That is, it looks at what is in the table cell and decides whether it is a number, a date etc. and formats it accordingly. Suppose you have a table and that it contains cigarettes smoked per day. It might show the ranges "01-05", "06-10" and "11-20". Excel will convert these into the dates "01-May", "06-October" and "20-November". It will drop trailing zeroes for numbers as well as drop leading zeroes. There are tricks you can use to prevent this but the correct method is to tell MS Office that the numeric fields are to be treated as text fields. This is done when you create the html file - it is not done when you read it in, which would be more convenient. The instruction is assigned to the variable mso-number-format. The "mso" at the start is short for "Microsoft Office". This variable is added automatically by %unistats and %unicatrep when you create html files. It is mentioned in the header of both macros as follows.

/ rrb 08May08         style(COLUMN)={HTMLSTYLE="mso-number-format:'\@'"} added
/                      to proc report call so that MS Office treats the cells as
/                      text-formatted cells. The same can be achieved using
/                      headtext="<style> td {mso-number-format:\@}</style>"
/                      in the ODS statement but it is hard to remember.

The code

What I will show you is the code I have used to produce both an ascii file and an html file, showing the same table. And I have copied the html file to give it an .xls extension so you can click on it and read it into Excel. I could have given the output file an extension of ".xls" in my code below, if I wanted to. It would not change the contents of the file in any way by giving it a different extension. Firstly, here is the code I ran to produce the output.

options ls=90;

proc format;
value trtnarr
1="Ambicor@(1g/day)"
2="Betamaxin@(500mg/day)"
3="No@treatment"
;
value agecat
    0-15="<16 yrs"
   16-25=" 16 - 25 yrs"
   26-40=" 26 - 40 yrs"
   41-65=" 41 - 65 yrs"
   66-high=">65 years"
   ;
run;

proc sort data=sasuser.demog(where=(fascd=1))
out=demog(drop=fascd);
by patno invid;
run;

data demog;
set demog;
agecat=age;
format trtcd trtnarr. agecat agecat.;
label sexcd="Gender"
        racecd="Race"
        agecat="Age (yrs)"
        age="A0"x
        weight="Weight (kg)"
        ;
run;

%popfmt(demog,trtcd,uniqueid=patno invid)

%unistats(dsin=demog,unicatrep=yes,total=yes,
odshtml=file="C:\spectre\unitest2.html",
lowcasevarlist=sexcd racecd,nbspaces=yes,outputwidthpct=calc,
stats=N Min Mean Max STD.,minfmt=3.,maxfmt=3.,compskip=yes,
varlist=sexcd racecd agecat age weight/m,
pvarlist=sexcd racecd agecat age weight,
allcatvars=racecd agecat);

ODS->html->Word

Microsoft Word can receive Excel output using copy and paste. But if we want to do things the optimum way then we can insert an html file directly into MS Word. It works much better that way. MS Word can understand html, which is hardly surprising, since you can save Word documents as html files. Html and Word documents have crossed into each other's territory. You will be able to see a Word document in the outputs in the next section where I have given the document two titles and then inserted the html file created by the above code.

ODS html vs. ODS rtf

You may wonder why I am not using ODS rtf to create output to be inserted into Word. It's for a very good reason. At the time of writing, ODS rtf does not work as well as ODS html. And the output from ODS html can easily be incorporated into both Excel and Word and retain the same look as it does as html (given the right circumstances). The only downside is that you have got to insert the html file into Word yourself.

The output

Here is the output. There is a text file, an html file, the html file copied and given the extension .xls and the Word document where I have inserted the html file. For the text file, you might see a mass of "a"s in the fields where the stats values are. This is the way some versions of browsers work. These "a"s are the non-breaking space character "A0"x that I have used to preserve alignment. To see this as it should then download the text file and view using Notepad (or Blocnotes).

text (you might have to download this file and read it using Notepad)
html
xls (this will only look correct in Excel)
doc (this will only look correct using Word)

Conclusion

You have seen that "fancy printer" output can be created using "ODS html" and that this html output can be read in by both Excel and Word where they can look good as "in-text" tables.

Go back to the home page.

E-mail the macro and web site author.