There are of course a few ways of doing this. One is using ODS CSV. The downside there, is that the file has to be named as a .csv for Excel to open it up properly. This causes problems if the user wants to alter the file in any way and then save it.
Another would be any of the other markup ODS destinations such as ExcelXP, HTML, MSOFFICE2K... These are all great, except for when you're working with large volumes of data. The file size can become unmanageable...
What we've done for quite some time to accommodate this, is use Proc DBLoad. You get an excel file, with the variable Labels used as headings instead of the variable names. Works great... The only problem now being that it only supports up to Excel95 format.
If you have the SAS Add-in for Microsoft Office, then you can select either variable names or labels to appear in Excel when you access a SAS data source.
If you know about using ODS CSV, then there is another alternative available to you. Microsoft wrote and supports a standard called Spreadsheet Markup Language (SpreadsheetML), which is an XML representation of an Excel workbook/worksheet. ODS supports creation of a SpreadsheetML XML file starting in SAS 9 using the following syntax:
ods tagsets.excelxp file='c:\temp\testML.xls'
proc print data=sashelp.shoes(obs=10) label;
var region subsidiary product sales;
proc means data=sashelp.shoes min median mean max;
ods tagsets.excelxp close;
If you have SAS 9 and Excel 2002 or higher, you should be able to open this file with Excel and see the labels in the proc print output. You should be able to name the file with a .xls extension on Windows and have Excel open the file with no problems. Users can make changes, etc and then when they save, they can either choose to save as "native" .xls format or choose to retain the XML format.
You can find out more about the ExcelXP tagset on the ODS Community web site at: http://support.sas.com/rnd/base
and follow the links to ODS MARKUP and the ExcelXP tagset.
Thanks Cynthia! Unfortunately the ExcelXP tagset has the same issue with performance as the other ODS destinations, once you start working with larger amounts of data. Anything over about 5k rows becomes unmanageable from a size perspective, as well as from a read-time perspective.
Proc Export works great as a simple data dump, but I was hoping there was a way to get the variable labels in there as opposed to just the variable names. Guess not...
how about renaming the variables to the labels after translating any blanks in the labels to underscores to make them syntactically valid (sas-wise) [proc contents or dictionary table read would be helpful here] and then proc exporting...
Oh, I forgot about DS2CSV. There used to be another macro called %FLATFILE that would also write out datasets (a copy of the macro is in this paper: http://www.bassettconsulting.com/BT3004.pdf -- otherwise, if you want to go forward with the XML approach, in order to change the root tag that is used for output from the SAS XML Libname Engine, you have to use PROC TEMPLATE to change the tagset template that is used by the Libname engine.
I support the concern about performance of ods tagsets.excelxp.
However the features provided by this tagset are too good to miss. So, I send the first 10 rows (of 10k, for example) through excelxp and the rest to csv directly with [pre] data _null_ ;
file 'remainder.csv' lrecl=10000 dsd ;
set our.data( firstobs= 11) ;
put ( _all_ )( ;
Add a var statement when you need columns in a different order from the default.
It is almost too simple and small to justify maintaining as a macro ;-)
The idea of outputting the labels separately (via ODS) from the data (via CSV or data _null_ flat file) is appealing - but there are some pitfalls associated with outputting long variable labels via ODS. Careful observers may find that long labels may wrap into multiple rows depending on the tagset involved. Through trial and error you can find solutions for whatever procedure you are using to generate the labels - a style override with cellwidth helps.
With ODS there is also the issue of the version of Excel to be used, making the choice of a tagset a difficult one. Older versions cannot read XML, so ExcelXP is out - MSOFFICE2K has some weirdnesses, as does the "vanilla" HTML destination. No easy answers!
All options have their drawbacks - and when working with medical claims data bases, these drawbacks come to the forefront. For example, Medicare provider numbers tend to be 6 digit (0 filled) CHARACTER fields, which sometimes have an E embedded in the 3rd position. Excel persists, stubbornly, in treating these values as numbers and exponential numbers in CSV and regular ODS output. Using a style override (HTMLSTYLE) in ODS output fixes the problem.
PROC EXPORT and the Microsoft Add-In aren't there for everyone - our PC versions of SAS have PROC EXPORT/IMPORT but not our server version of SAS.
I tend to use the ODS method despite the file size issues (you can always "save as" later to reduce the size, break into pieces and save as, etc.) because the style overrides and/or table templates you can use afford great flexibility in your output.
Another solution would be to create a format with the Variable Labels and use that instead of the Variable names.
A short example follows:
1. Define the format
2. Use the proc contents to retrieve the variable names in your dataset
proc contents data=sashelp.class out=tmp(keep=name);run;
3. Create a macro variable with the concatenation of the Variable names and the Variable labels
proc sql noprint;
select distinct catx(" ", name,"=",put(name, $vnames.)) into :varss
separated by ' '
4. Create a dataset in which you rename the variables with their labels
5. Create the xls file via HTML.
ods html file="c:\junk\test.xls";
proc print data=test;
ods html close;
The only problem is that at the end you have to get read the underscores manually.
Use the Excel libname engine in conjunction with the DSLABEL=YES. Works very nice. See http://stratia.ca/papers/excel_libname.pdf for an example of writing SAS data to Excel workbooks, including formatted output and sources for pivot tables.