The SAS Output Delivery System and reporting techniques

Preventing merged cells in results viewer

Reply
Frequent Contributor
Posts: 129

Preventing merged cells in results viewer

How can I prevent merged cells in the SAS results viewer? For example, I'm creating a table like the following using proc tabulate

------ Mean ------
year
2001 1.2
2002 2.2

When using the 'default' or 'listing' format, the blank cell to the right of the column heading 'year' is merged with the cell underneath it ('1.2'). This carries across to Excel if I cut and paste from the results viewer. I turn, this means that I cannot select the first two columns of the table to create an Excel graph, because the number 1.2 will get lined up with 'year' rather than '2001'.

I recall that if I use
ods msoffice2k file= etc
then I get an output file where this merging doesn't take place. Can I get the same result in the Results viewer?
N/A
Posts: 0

Re: Preventing merged cells in results viewer

The problem has nothing to do with the ods style or result window.
This behavior is the default of Proc Tabulate.
In order to prevent this merged cell I suggest the following "trick":

Proc tabulate data = ...
class ...
var ...

table year=' ' ,
dept*sales*mean
/ box = "year";
;

Notice that the label for year in the table statement is left blank and the label is inserted in the box part of the output;
Frequent Contributor
Posts: 129

Re: Preventing merged cells in results viewer

Thanks. Yes, I can see that I can avoid the problem by not showing 'year' where it normally appears and putting it in the box. However, this is a bit of a kludge, and is not very convenient for more complicated tables (where having the variable name or label in place is convenient).

As I noted in my original post, the command 'ODS msoffice2k' can be used to create an output excel file (actually an html file) which doesn't have this problem. Is it possible to have this ODS style available in the results window?
SAS Super FREQ
Posts: 8,745

Re: Preventing merged cells in results viewer

Hi:
The short answer is NO. The results window or the LISTING window is a "monospace" destination -- that just means that output looks pretty much the same as it looked when SAS was originally created and the LISTING window, the .LST file or the SYSOUT file were the ONLY way of getting printable output from SAS.

ODS HTML (and by extension, any of the HTML destinations -- ODS MSOFFICE2K, ODS TAGSETS.MSOFFICE2K_X, ODS PHTML, ODS CHTML, ODS HTMLCSS, etc) were designed to be opened and rendered in a browser (first) or in other applications, such as Word or Excel that can open and render HTML tags.

If you think the PROC TABULATE approach is too kludgey, then you might try PROC REPORT -- which has the advantage of not having the BOX area at the intersection of the Column header area and the Row header area.

ODS MSOFFICE2K is not an ODS STYLE-- it is an ODS DESTINATION. The LISTING destination is what controls the output or LISTING window. For tabular output, the LISTING destination does not support the use of STYLE templates to control the look and feel of the output. You can alter the way your HTML (or other non-LISTING destination) output looks by using the STYLE= option in different destinations:
[pre]
ods msoffice2k file='see_sasweb.html' style=sasweb;
OR
ods msoffice2k file='see_ocean.html' style=ocean;
OR
ods rtf file='see_journal.rtf' style=journal;
OR
ods pdf file='see_journal.pdf' style=journal;
[/pre]

The 2 files will look very different from one another because the STYLE= option sends appropriate HTML style information to the output HTML file. Even if you open the HTML file with Excel (or name the file with .XLS as the file extension), the style information is still HTML-based style info. The LISTING destination, on the other hand is plain text ASCII output -- as far as tabular results go. The only time you can use the STYLE= option with the LISTING destination is if you plan to create graphic output in SAS 9.2 and you want the GRAPH1 window or your ODS graph output to use the SAS Style template.

cynthia
Frequent Contributor
Posts: 129

Re: Preventing merged cells in results viewer

Thanks Cynthia for clarifying the difference between a style and destination.

However, you say that "The results window or the LISTING window is a "monospace" destination". This is not the case on my system. The Results viewer window is html formatted output (the output window is monospace).

Similarly, you state that "For tabular output, the LISTING destination does not support the use of STYLE templates to control the look and feel of the output.". But presumably this is not the case for the "Results viewer" window (does this count as a destination?) - since under the Results tab in Preferences I can choose a style for the results.

So I suppose my question amounts to asking whether I can customize the Results viewer destination to make it like the msoffice2k destination. I'm guessing the answer is again No.
SAS Super FREQ
Posts: 8,745

Re: Preventing merged cells in results viewer

Hmmm...are you by any chance using SAS Enterprise Guide??? I believe one of the default output types in SAS EG is HTML or SAS Report and in that case, the output is rendered as HTML or as though it is HTML and rendered as a browser would render it.

And, if you are using SAS EG, then the Results viewer is -different- from the LISTING destination/window. I apologize for not clarifying or asking the question about whether you were using EG or Display Manager.

When you use SAS EG, behind the scenes, you will see ODS statements being issued -- if you look in the SAS log -- you will see ODS HTML or ODS SASREPORT statements surrounding all your code. For EG, the Results Viewer simply surfaces the output -- rendering it in whatever form (using whatever internal transformation or plug-in, it needs to use to load the output into the Results Viewer). You can actually turn on the LISTING output in SAS EG, but folks don't do that very often -- given that it looks plain, plain, plain.

And, you're right, because EG treats the output like a browser would treat the output (and not like Excel treats the output), you may or may not get the look you want in the EG Results Viewer. One thing you can try is this -- from a CODE node in EG:
[pre]
ods _all_ close; /* close any "automatic" EG open destinations */
ods msoffice2k file='somefile.html' style=sasweb;

** insert some TABULATE code here;

ods msoffice2k close;
[/pre]

And one of 2 things will happen 1) either your output will look the same in the EG Results Viewer as it does in Excel (Yay!) or 2) your output will look different in the EG Results Viewer than it does when Excel opens the HTML file (Bummer!)

If it is #1, then you can figure out using EG options how to change your results from either SAS Report (XML) or "vanilla" HTML to MSOFFICE2K flavor of HTML. If it is #2, then there's not much point using a code approach to get MSOFFICE2K flavor of HTML.

Just a suggestion.

cynthia
Frequent Contributor
Posts: 129

Re: Preventing merged cells in results viewer

Sorry, I should have specified the system. No I'm not using EG. I'm using SAS 9.2 (TS1M0) on Windows XP. I'm pretty sure I'm using the default setup.

However, your suggested code is very helpful. Eg if I go
[pre]ods _all_ close;
ods msoffice2k;
- tabulate statements
ods msoffice2k close;
[/pre]
Then my Results Viewer window is in the desired format !!

The only problem is that this creates a html output file in the same folder as my sas program instead of the work directory - I guess I can put a file statement to relocate it.

Now I'm wondering how to make this the default.
SAS Super FREQ
Posts: 8,745

Re: Preventing merged cells in results viewer

Hi:
You can use a fully qualified path name in your ODS invocation statement and the ID= suboption to create multiple versions of the same file.
[pre]
ods msoffice2k (id=1) file='c:\temp\htmlvers.html' style=sasweb;
ods msoffice2k (id=2) file='c:\myreport\forExcel.xls' style=sasweb;

**** your code;

ods _all_ close;
[/pre]

In the above code, the ID=1 file would be written to c:\temp folder and the ID=2 file would be written to c:\myreport folder.

On Windows with Display Manager, the default behavior is to use an internal viewer for showing HTML, RTF and PDF output. There is also a setting you can check under Tools --> Options --> Preferences --> Results that says to automatically create LISTING and/or HTML output.

On that screen is the place where you can choose a default folder for the output. I don't think you can change the destination (from ODS HTML to ODS MSOFFICE2K), but you can change the directory. I always turn this setting off because I like to control the name of the output files I create and using this choice just makes numbered files for you. I'm just too much of a control person. I like to explicitly show in the code the path and the name of the files I'm creating and using the ID= suboption allows me to create as many files as I want and control the locations and names.

cynthia
SAS Employee
Posts: 174

Re: Preventing merged cells in results viewer

SAS 9.3 TABULATE has a new TABLE statement NOCELLMERGE option to address your situation. I know that won't help you now.

-- David Kelley, SAS
N/A
Posts: 0

Re: Preventing merged cells in results viewer

Thanks for the inside information about 9.3 ...
Meanwhile I think that among all the complex ways listed above , the most easy and versatile way is by using the "Trick" that I have suggested. It creates the same output in all ODS destinations (Listing, ExcelXp, ...) no matter what style is being used.
Ask a Question
Discussion stats
  • 9 replies
  • 1271 views
  • 0 likes
  • 4 in conversation