Quick View in Excel

[djbateman]

I recently upgraded from SAS 9.1.3 to SAS 9.3.  Whenever I wanted a quick view of my SAS datasets in Excel, I could right-click the table in the Explorer window and choose "View in Excel".  It would then bring up an Excel file containing my data with any saved titles on top.  The column names were the same as the variable names.  Now when I choose the "View in Excel" option, it displays the variable labels instead of the variable names.  This is more informative for when I am sending the data to other people, but it is quite annoying in that if the label wraps, it creates a new row in Excel.  This makes it a hassle for filtering, for part of the variable label is now included in the filtering options.  Is there a way to adjust the settings so that either the variable name is displayed in place of the variable label or that the variable label is all kept in the same cell?  I have attached a sample below so you can see what I mean with the headers.  The headers should be bold.

[ballardw · Accepted solution]

A quick test in SAS 9.2.3 show if I set

options nolabel;

before doing the View in Excel it shows variable names instead of the label.

Reset with:

options label;

afterwards.

[Peter_C]

djbateman

ballardw provides the solution for the transition to SAS9.3 where the column headings of the quick "view in excel" use the variable label by  default

And it is a "quick solution" for a "quick view" - probably the quickest!

However reading between the lines of the original posting,

     .....          "when I am sending data to other people"

is not really asking for a "quick view" but for something you are happy to provide for others.

To achieve this, I (like most who know about it) recommend writing an excel formatted workbook with the ODS MARKUP destination,  tagsets.excelxp.

If you have not come across this before you might find daunting, the sheer scope of the capability. However, it can be quite simple to start with. The big advantage (imho) is that you are taking control of how excel will format the cells.

Many who are new to excelxp criticize the physical size of the workbook created. If the size becomes a problem then you can find additional processing that can re-save the as a binary format excel workbook (which compresses the data and formatting tags).

The quick "view in excel" is fine for a quick look for yourself, but when providing data for others (more than once) I would allways recommend ODS TAGSETS.EXCELXP.

peterC    

[Cynthia_sas]

And, in addition to Peter's suggestion, this paper by Rick Andrews is a very good introduction to TAGSETS.EXCELXP and the extensive list of suboptions that you can use to format the results: http://www.nesug.org/proceedings/nesug08/ap/ap06.pdf

cynthia

[evp000]

WARNING: using the NOLABEL option means that the variables in your datasets will have no labels!!!  Remember to set the option back to LABEL afterwards. I just lost a lot of time discovering this.