DATA Step, Macro, Functions and more

How to Change Formats when Exporting from SAS to Excel

Accepted Solution Solved
Reply
Regular Contributor
Posts: 237
Accepted Solution

How to Change Formats when Exporting from SAS to Excel

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!


Accepted Solutions
Solution
‎12-08-2016 05:01 PM
Super User
Posts: 10,023

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

Posted in reply to djbateman
As Reeza pointed out use TAGATTR.

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


View solution in original post


All Replies
Super User
Posts: 11,343

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

Posted in reply to djbateman

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.

Regular Contributor
Posts: 237

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

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

Super User
Posts: 19,789

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

Posted in reply to djbateman

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. 

Solution
‎12-08-2016 05:01 PM
Super User
Posts: 10,023

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

Posted in reply to djbateman
As Reeza pointed out use TAGATTR.

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


☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 256 views
  • 0 likes
  • 4 in conversation