BookmarkSubscribeRSS Feed
deleted_user
Not applicable
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.

Anybody know of another way to do this??

Thanks in advance...
10 REPLIES 10
Cynthia_sas
SAS Super FREQ
Hi!
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:

[pre]
ods tagsets.excelxp file='c:\temp\testML.xls'
style=sasweb;
proc print data=sashelp.shoes(obs=10) label;
var region subsidiary product sales;
run;

proc means data=sashelp.shoes min median mean max;
var sales;
class region;
run;
ods tagsets.excelxp close;
[/pre]

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.
cynthia
deleted_user
Not applicable
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...

Thanks,
Darryl
deleted_user
Not applicable
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...
sbb
Lapis Lazuli | Level 10 sbb
Lapis Lazuli | Level 10
Consider using the DS2CSV macro, now available with SAS Base, starting with SAS version 9. Another option is to generate the CSV with a DATA step, then processing is under your complete control.

Also, any file can be opened with Excel, using FILE->OPEN and show ALL FILE TYPES. The double-click file technique requires the file association.

Scott Barry
SBBWorks, Inc.

http://support.sas.com/documentation/cdl/en/lrdict/59540/HTML/default/a002683390.htm
Cynthia_sas
SAS Super FREQ
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.

cynthia
Peter_C
Rhodochrosite | Level 12
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_ )(:) ;
run;[/pre]
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 😉


PeterC
louisehadden
Quartz | Level 8
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.
qkaiwei
Calcite | Level 5
Easier with SAS/Access to PC-File module.

-------------------------------------------------------------------------------------
data tmp;
set sashelp.class;
label name='new name' sex='new sex' age='new age' height='new height' weight='new weight';
run;

libname xls "c:\123.xls";
data xls.'new sheet'n(dblabel=yes);
set tmp;
run;
libname xls clear;
deleted_user
Not applicable
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
proc format;
value $vnames
Name='new_name'
Sex='new_sex'
Age='new_age'
Height='new_height'
Weight='new_weight';
run;
2. Use the proc contents to retrieve the variable names in your dataset
proc contents data=sashelp.class out=tmp(keep=name);run;
proc print;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 ' '
from tmp;
quit;
run;

%put &&varss;

4. Create a dataset in which you rename the variables with their labels
data test;
set sashelp.class;
rename &&varss;
run;
5. Create the xls file via HTML.
ods html file="c:\junk\test.xls";
proc print data=test;
run;
ods html close;

The only problem is that at the end you have to get read the underscores manually.
Harry
Obsidian | Level 7
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.

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!

Discussion stats
  • 10 replies
  • 5686 views
  • 0 likes
  • 7 in conversation