BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
djbateman
Lapis Lazuli | Level 10

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!

1 ACCEPTED SOLUTION

Accepted Solutions
Ksharp
Super User
As Reeza pointed out use TAGATTR.

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


View solution in original post

4 REPLIES 4
ballardw
Super User

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
Lapis Lazuli | Level 10

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

Reeza
Super User

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
Super User
As Reeza pointed out use TAGATTR.

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


sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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