topic Retaining digits as character in Excel in ODS and Base Reporting

Retaining digits as character in Excel

natanya — Tue, 24 Aug 2010 14:37:10 GMT

Hello,
I am using an ods html statement to move output from proc print into MS Excel. One variable is 5 digits, which is defined as character (created using put statement). When it moves to Excel, it reverts to numeric. Thus I loose any leading zeros. How can I retain the 5 digits as character in excel?
Thanks

Re: Retaining digits as character in Excel

sbb — Tue, 24 Aug 2010 15:18:03 GMT

Suggest checking the forum archives, also the SAS support http://support.sas.com/ website for SAS-hosted DOC and supplemental technical / conference material.

Scott Barry
SBBWorks, Inc.

Suggested Google advanced search argument, this topic / post:

export excel retain leading zeros site:sas.com

Re: Retaining digits as character in Excel

Cynthia_sas — Tue, 24 Aug 2010 15:24:19 GMT

Hi:
As Scott suggests, searching the forum archives is a great idea, since there have been many previous forum postings on this topic. Basically, Excel does not respect SAS formats for leading zeroes -- even if the number is a character value in the SAS end of things, if Excel detects numbers, it applies a General number format to the cell which strips any leading zeroes. This is Microsoft behavior and the only way to impact the behavior is to send a Microsoft format from SAS to Excel.

You can either use a format to tell Excel that the number is a character variable or you can use a format to just put leading zeroes into the number. With HTML-based destinations, the HTMLSTYLE= style attribute is the way you would send a Microsoft format from SAS to Excel.

These two previous forum postings show how to use HTMLSTYLE:
http://support.sas.com/forums/thread.jspa?messageID=41019ꀻ
http://support.sas.com/forums/thread.jspa?messageID=8753∱

Do note that in SAS 9, you may want to use ODS MSOFFICE2K instead of ODS HTML -- because MSOFFICE2K uses Microsoft flavor of HTML tags and Excel is "happier" rendering those tags instead of ODS HTML tags which are HTML 4.0 tags (and Excel doesn't like those tags as well).

cynthia