topic How to Change Formats when Exporting from SAS to Excel in SAS Programming

How to Change Formats when Exporting from SAS to Excel

djbateman — Wed, 07 Dec 2016 17:18:11 GMT

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!

Re: How to Change Formats when Exporting from SAS to Excel

ballardw — Wed, 07 Dec 2016 18:19:01 GMT

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.

Re: How to Change Formats when Exporting from SAS to Excel

djbateman — Wed, 07 Dec 2016 18:21:58 GMT

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

Re: How to Change Formats when Exporting from SAS to Excel

Reeza — Wed, 07 Dec 2016 18:54:47 GMT

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.

Re: How to Change Formats when Exporting from SAS to Excel

Ksharp — Thu, 08 Dec 2016 02:47:09 GMT

As Reeza pointed out use TAGATTR.

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