BookmarkSubscribeRSS Feed
SASPhile
Quartz | Level 8
If there is a leading zero for zip code or phyid,it is getting dropped in excel.I'm using ods and proc report.How to retain the format of the variables as they are in SAS dataset when exported to excel.
13 REPLIES 13
sbb
Lapis Lazuli | Level 10 sbb
Lapis Lazuli | Level 10
Suggest searching the SAS forums and the support website - the topic has been addressed before.

Scott Barry
SBBWorks, Inc.
Suggested Google advanced search argument, this topic / post:

leading zero zip code export excel site:sas.com
Cynthia_sas
SAS Super FREQ
Hi:
Depending on how you are getting your results into Excel (ODS HTML, ODS CSV or ODS TAGSETS.EXCELXP or DDE or PROC EXPORT) you may or may not be able to get Excel to show leading zeroes. For ODS HTML and ODS TAGSETS.EXCELXP -- this is very do-able using HTMLSTYLE or TAGATTR style attributes.

As Scott suggests, searching previous forum postings may help you out here.

cynthia
sbb
Lapis Lazuli | Level 10 sbb
Lapis Lazuli | Level 10
Frankly, I would say that it more depends on zip-plus-4 sometimes being encountered in the GUESSINGROWS data-window, which tends to create a real pickle.

Scott Barry
SBBWorks, Inc.
Cynthia_sas
SAS Super FREQ
Leading zeroes and Excel are just an issue in general, whether you're talking about IMPORT (Excel to SAS dataset) or EXPORT (SAS dataset to Excel) or ODS (SAS output to Excel). At least with some ODS methods, even if Excel won't respect the SAS format, you DO have a chance to send the Microsoft format from SAS to Excel.

Since the OP did not say HOW he was using ODS (CSV, HTML, MSOFFICE2K, TAGSETS.EXCELXP), it's hard to respond in more detaiil. But your idea to search the forum was a good one. I know that I've posted the leading zero solution a couple of times. And I know that it's been in several papers by the folks in Tech Support and others.

cynthia
SASPhile
Quartz | Level 8
This is a good fix for leading zeros issue in when exporting to excel using proc report ODS.

define zip1 / group '5 digit zip'
STYLE (header) = {just=left font_size=1}
STYLE (COLUMN) = {htmlstyle="mso-number-format:\@" just=left font_size=1.5};
Cynthia_sas
SAS Super FREQ
Yes, that's exactly the example. It is not specific to PROC REPORT, however. The Microsoft format technique will work with PROC REPORT, PROC PRINT, PROC TABULATE, and in a TABLE template or a STYLE template. The syntax you show is the PROC REPORT syntax, but similar syntax (not exactly the same) will work with other procedures. For example, the PROC PRINT equivalent is shown here:
http://support.sas.com/kb/32/414.html

In your example, the @ tells Excel that you want the number with leading zeroes to be treated as a text string. If you wanted to have a numeric value display leading zeroes, but remain numeric, then you would do:
[pre]
STYLE (COLUMN) = {htmlstyle="mso-number-format:00000"};
[/pre]

But the HTMLSTYLE approach will only work for HTML-based ODS destinations (ODS HTML, ODS MSOFFICE2K, ODS HTMLCSS, etc).

When you use ODS to create files for Excel to open and render, you are not creating true, binary Excel files -- such as you would create with PROC EXPORT or the Excel LIBNAME engine. ODS is only creating ASCII text files that Excel knows how to open and render. So the instructions that you send for a Microsoft format must be in either an HTML (mso-number-format) or an XML specification.

HTML and XML are file types that are openable with Excel -- but they are not "true" .xls or .xlsb files. So, if you name your ODS file with a .XLS extension, Excel will grumble a bit before opening the file in Office 2007 and 2010 with the warning:
The file you are trying to open, '[filename]', is in a different format than
specified by the file extension. Verify that the file is not corrupted and is
from a trusted source before opening the file. Do you want to open the file
now?


Microsoft's thinking behind the warning are described here:
http://blogs.msdn.com/b/vsofficedeveloper/archive/2008/03/11/excel-2007-extension-warning.aspx

So given that you are creating HTML tags with ODS HTML or MSOFFICE2K, the HTMLSTYLE puts a Microsoft format style property (mso-number-format) into the HTML tags being generated so that when the file is rendered by Excel -- it has the instructions for spreadsheet formatting.

TAGSETS.EXCELXP does use a Microsoft format, but via a different mechanism on the SAS side because the XML tags required for Spreadsheet Markup Language XML follow a different form than the HTML. So if you were going to want leading zeroes respected with ODS TAGSETS.EXCELXP, you would do:

[pre]
STYLE (COLUMN) = {tagattr="Format:@"};
OR
STYLE (COLUMN) = {tagattr="Format:00000"};
[/pre]

Some other useful Microsoft formats are listed on page 2 of this paper:
http://www2.sas.com/proceedings/sugi28/012-28.pdf

cynthia
SASPhile
Quartz | Level 8
Thats true.But so far i'm not sucessful in using TAGSETS.EXCELXP .Thats the reason I resort to HTML-based ODS destinations.
Cynthia_sas
SAS Super FREQ
Sorry to hear that (not successful with TAGSETS.EXCELXP). Both of the programs below work for me.

The only thing that I can think of not working in TAGSETS.EXCELXP is using SAS/GRAPH programs, since it is MICROSOFT that did not allow graphic images in the Spreadsheet Markup Language XML.

cynthia
[pre]
data phid;
length name address $15;
infile datalines;
input phid name $ zip $ qty;
return;
datalines;
1001 John 10103 25
1002 Adams 00123 30
1002 Adams 02001 30
1003 Mark 28546 30
;
run;

title; footnote;

ods listing close;
ods msoffice2k file='c:\temp\use_MS_format_ht.xls' style=sasweb;

proc report data=phid nowd;
title '2) Use HTMLSTYLE to send a MICROSOFT format to Excel';
column phid name zip qty;
define phid / display
style(column)={htmlstyle="mso-number-format:000000"};
define name /display;
define zip / display
style(column)={htmlstyle="mso-number-format:00000"};
define qty / sum;
run;

ods _all_ close;


ods listing close;
ods tagsets.excelxp file='c:\temp\use_MS_format_xp.xls'
style=sasweb options(doc='Help');

proc report data=phid nowd;
title '3) Use TAGATTR to send a MICROSOFT format to Excel';
column phid name zip qty;
define phid / display
style(column)={tagattr="Format:000000"};
define name /display;
define zip / display
style(column)={tagattr="Format:00000"};
define qty / sum;
run;

ods _all_ close;

title;

[/pre]
SASPhile
Quartz | Level 8
When TAGSETS.EXCELXP is used it supresses the gridlines in excel.
Cynthia_sas
SAS Super FREQ
Oh, that's odd. When I run the code, my table does have interior grid lines. The rest of the area outside my table does not have grid lines, but that's in sync with how the output looks in Print Preview.

cynthia
Cynthia_sas
SAS Super FREQ
Also, searching on support.sas.com finds this Tech Support note that explains how a style template can allow you to see the gridlines when you use TAGSETS.EXCELXP.
http://support.sas.com/kb/38/282.html

cynthia
SASPhile
Quartz | Level 8
When I run the code and try to open temp.xls,it gives a message "file in use".
sbb
Lapis Lazuli | Level 10 sbb
Lapis Lazuli | Level 10
At this point, you will need to share your code, preferably as it appears in a SAS log output with all code revealed. Likely you have an open FILENAME or ODS, if your SAS session is still active when you attempt to open the XLS file -- it's unclear whether you get an Excel error or a SAS error, as explained?

Scott Barry
SBBWorks, Inc.

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
  • 13 replies
  • 1190 views
  • 0 likes
  • 3 in conversation