BookmarkSubscribeRSS Feed
mmagnuson
Quartz | Level 8

Hello, I've searched high and low and haven't found the answer that will help me solve this problem.


In SAS there are multiple variables that I am using that are numeric.  When I use the "Send To Excel" export function I seems to hold the "numeric" format.  However, when I Export as a Step in Project and then use a data connection into an Excel Table it pulls in most of the numeric fields but makes 3 numeric variables as characters.  If I want them to be used as numbers I have to use the "Text to Columns" function.


I also have re-imported the excel workbook that gets exported, set the formats to exactly the same values as the other numeric variables then re-exported it and the same thing happens. I tired to even create new computed columns to see if that would work and it has not.

 

Any thoughts would be very helpful! I'm not sure why it does this for only some of the numeric variables.

 

Thanks!

4 REPLIES 4
ballardw
Super User

Example data 1) as a data set (SAS data) in the form of data step code so we can see actual properties and values and 2) of the exported data without actually opening in Excel.

 

Instructions here: https://communities.sas.com/t5/SAS-Communities-Library/How-to-create-a-data-step-version-of-your-dat... will show how to turn an existing SAS data set into data step code that can be pasted into a forum code box using the {i} icon or attached as text to show exactly what you have and that we can test code against.

 

Also if you provide the log generated from the "data connection into an Excel Table" that may help.

 

 

 

 

TomKari
Onyx | Level 15

I have a feeling that the formats on the variables are causing the problem. Try removing the formats from the variables, and then exporting.

 

Tom

mmagnuson
Quartz | Level 8
Hi, I tried to remove the formats but it still didn’t work. I’ll try to copy/paste the code on Monday
CaseySmith
SAS Employee

Also, when you use Export as a Step in Project, are you exporting to .xlsx I assume?  Or .xls?

 

There are two main ways in which EG will "send/export data" to Excel, each with advantages/disadvantages...

 

1) Favor value integrity.  Insert raw values into Excel cells and then apply Excel formats that map as close as possible to the SAS formats associated with each column.  Ensures raw values are preserved, but may not display the same in Excel as they do in SAS, since there is not a 1-to-1 mapping of SAS-to-Excel formats.

 

2) Favor display integrity.  Insert formatted values into Excel cells and let Excel interpret the format.  (Similar to manually typing a value into a cell whose format is General.)  Ensures the displayed value in Excel will be more consistent with the displayed value in SAS, but may not be a recognized format by Excel and may not retain the underlying raw value.

 

Casey


Register today and join us virtually on June 16!
sasglobalforum.com | #SASGF

View now: on-demand content for SAS users

Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
SAS Enterprise Guide vs. SAS Studio

What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.

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
  • 1790 views
  • 2 likes
  • 4 in conversation