DATA Step, Macro, Functions and more

Quick View in Excel

Accepted Solution Solved
Reply
Regular Contributor
Posts: 220
Accepted Solution

Quick View in Excel

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.


Accepted Solutions
Solution
‎01-25-2013 05:24 PM
Super User
Posts: 10,483

Re: Quick View in Excel

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.


View solution in original post


All Replies
Solution
‎01-25-2013 05:24 PM
Super User
Posts: 10,483

Re: Quick View in Excel

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.


Valued Guide
Posts: 2,174

Re: Quick View in Excel

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    

SAS Super FREQ
Posts: 8,742

Re: Quick View in Excel

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

Contributor
Posts: 74

Re: Quick View in Excel

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. 

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 1777 views
  • 8 likes
  • 5 in conversation