How to Change Formats when Exporting from SAS to Excel

[djbateman]

I have data that I am exporting from SAS to Excel for our data managers.  There are a couple of issues that I am noticing that I cannot correct.  I want to see if there is a way to change this in SAS instead of manually doing it in Excel.

First, we have site numbers that are 3-digit character values, usually containing leading zeros.  For example, site 001, 002, 003, etc.  When it exports into Excel, it displays as numeric 1, 2, 3, etc.  Is there a way to keep the leading zeros in Excel?  If it can be retained as a character value, that would be ideal, but not necessary if we can at least keep the leading zeros.

FYI, I have tried adding to the SAS variable an apostrophe since Excel uses an apostrophe to treat a numeric value as a character string, but that does not work.

Second, I have long ID variables containing values like "5086074278593750", but they display in Excel as "5.08607E+15".  I want to ignore the scientific notation and display it as the raw value.  I can manually change the format in Excel from general to number and then remove the 2 decimal places, but I don't want to have to do that every time to every variable that is reformated.

In short, I want my data to show up in Excel exactly as I have it in SAS.  Not format changes.  Any solutions would be much appreciated!

[ballardw]

HOW are you exporting to Excel?

Are you  exporting a data set, the result of a report procedure (Procs Print, Report and Tabulate) or something else?

Different content is likely to require different options.

[djbateman]

I am typically using ods tagsets.excelxp to export a table through PROC PRINT or PROC REPORT (usually PROC PRINT).

[Reeza]

Here's an index of the features you want and where you can find sample code that implements that feature. 

http://www.sas.com/events/cm/867226/ExcelXPPaperIndex.pdf

Leading zeroes is halfway down the page. 

[Ksharp · Accepted solution]

As Reeza pointed out use TAGATTR.

proc report......
define x /style={tagattr='format:text'}