DATA Step, Macro, Functions and more

How to Change Formats when Exporting from SAS to Excel

Accepted Solution Solved
Reply
Regular Contributor
Posts: 220
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: 9,676

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

As Reeza pointed out use TAGATTR.

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


View solution in original post


All Replies
Super User
Posts: 10,497

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

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: 220

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: 17,818

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

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: 9,676

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

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
  • 215 views
  • 0 likes
  • 4 in conversation