topic Re: Excel & proc report in SAS Programming

Excel & proc report

SASPhile — Thu, 07 Oct 2010 17:39:14 GMT

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.

Re: Excel & proc report

sbb — Thu, 07 Oct 2010 17:44:11 GMT

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

Re: Excel & proc report

Cynthia_sas — Thu, 07 Oct 2010 21:14:56 GMT

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

Re: Excel & proc report

sbb — Thu, 07 Oct 2010 23:56:34 GMT

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.

Re: Excel & proc report

Cynthia_sas — Fri, 08 Oct 2010 00:59:24 GMT

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

Re: Excel & proc report

SASPhile — Fri, 08 Oct 2010 13:36:01 GMT

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};

Re: Excel & proc report

Cynthia_sas — Fri, 08 Oct 2010 15:40:49 GMT

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

Re: Excel & proc report

SASPhile — Fri, 08 Oct 2010 16:30:00 GMT

Thats true.But so far i'm not sucessful in using TAGSETS.EXCELXP .Thats the reason I resort to HTML-based ODS destinations.

Re: Excel & proc report

Cynthia_sas — Fri, 08 Oct 2010 19:26:23 GMT

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]

Re: Excel & proc report

SASPhile — Fri, 08 Oct 2010 20:16:02 GMT

When TAGSETS.EXCELXP is used it supresses the gridlines in excel.

Re: Excel & proc report

Cynthia_sas — Fri, 08 Oct 2010 20:35:41 GMT

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

Re: Excel & proc report

Cynthia_sas — Sat, 09 Oct 2010 02:37:27 GMT

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

Re: Excel & proc report

SASPhile — Mon, 11 Oct 2010 13:57:48 GMT

When I run the code and try to open temp.xls,it gives a message "file in use".

Re: Excel & proc report

sbb — Mon, 11 Oct 2010 14:57:59 GMT

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.