topic Re: Exporting numeric variable to Excel Table but shows as character in SAS Enterprise Guide

Exporting numeric variable to Excel Table but shows as character

mmagnuson — Fri, 27 Sep 2019 14:00:05 GMT

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!

Re: Exporting numeric variable to Excel Table but shows as character

ballardw — Fri, 27 Sep 2019 15:07:25 GMT

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-data-AKA-generate/ta-p/258712 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.

Re: Exporting numeric variable to Excel Table but shows as character

TomKari — Fri, 27 Sep 2019 15:57:11 GMT

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

Re: Exporting numeric variable to Excel Table but shows as character

mmagnuson — Sat, 28 Sep 2019 22:07:32 GMT

Hi, I tried to remove the formats but it still didn’t work. I’ll try to copy/paste the code on Monday

Re: Exporting numeric variable to Excel Table but shows as character

CaseySmith — Tue, 26 Nov 2019 21:52:05 GMT

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