BookmarkSubscribeRSS Feed
BruceBrad
Lapis Lazuli | Level 10
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?
10 REPLIES 10
deleted_user
Not applicable
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;
BruceBrad
Lapis Lazuli | Level 10
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?
Cynthia_sas
SAS Super FREQ
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
BruceBrad
Lapis Lazuli | Level 10
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.
Cynthia_sas
SAS Super FREQ
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
BruceBrad
Lapis Lazuli | Level 10
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.
Cynthia_sas
SAS Super FREQ
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
David_SAS
SAS Employee
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
deleted_user
Not applicable
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.
SuperFox
Calcite | Level 5

I have been tring to get nocellmerge to work but couldn't.  Could you let me know what's wrong?

 

Proc Tabulate Data=myData FORMAT=Comma10.0;

CLASS Class1 Class2 / Missing;

Table Class1 , N*Class2 / Nocellmerge;

Title 'Mytitle';

Run;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 10 replies
  • 4014 views
  • 0 likes
  • 5 in conversation