topic Re: Exporting to Excel converts text to an exponential number in SAS Enterprise Guide

Exporting to Excel converts text to an exponential number

MarkODwyer — Fri, 15 Nov 2019 00:41:10 GMT

Is there a way of preventing SAS EG (7.15) from converting a text field in the format 1234561E1 to an exponential number when exporting to Excel?

Re: Exporting to Excel converts text to an exponential number

ballardw — Fri, 15 Nov 2019 00:58:01 GMT

Have you tried making the column wider in Excel?

Also, it may help to show the code you are using to "export" the data.

Re: Exporting to Excel converts text to an exponential number

Reeza — Fri, 15 Nov 2019 03:00:41 GMT

Not if you're just right clicking and selecting export. Otherwise yes, you can control the types of how you export quite closely but it takes some specifications/work sometimes.

One way is to make sure the cell width is large enough - and check if Excel isn't doing the conversion on its own.

@MarkODwyer wrote:

Is there a way of preventing SAS EG (7.15) from converting a text field in the format 1234561E1 to an exponential number when exporting to Excel?

Re: Exporting to Excel converts text to an exponential number

Kurt_Bremser — Fri, 15 Nov 2019 07:19:56 GMT

Exporting from EG via a task is a two-fold process. First, EG sends code that writes the data to a specifically structured textfile. Next, that file is fed to a data access module (supplied by Microsoft) that "reads" the data into Excel. During that "read" (which is basically an automation of somebody typing in the data), data that looks like a number/date/time is automatically converted.

Re: Exporting to Excel converts text to an exponential number

MarkODwyer — Sun, 17 Nov 2019 22:30:55 GMT

Thanks Kurt,

I agree that the automatic conversion is probably happening during the read into Excel. Is there a way of saving the textfile that is sent from SAS? If so I could manually read that textfile into Excel and specify that the field is text.

Just to be clear, I am creating a dataset from the Query Builder. Then from the 'Results' tab I select 'Send To', "Microsoft Excel'. There is no option that i can see to 'Send To' a textfile.

Re: Exporting to Excel converts text to an exponential number

SASKiwi — Sun, 17 Nov 2019 23:44:53 GMT

@MarkODwyer - How about using Excel's "Text to Columns" feature after sending to Excel? You can explicitly set a text column there.

Re: Exporting to Excel converts text to an exponential number

MarkODwyer — Sun, 17 Nov 2019 23:59:04 GMT

SASKiwi,

Once it has been sent to Excel It is too late to convert it back to text. For example 4526241E1 becomes 4.53E+07 when it gets sent to Excel. 'Text to columns' changes it to 45262410, not 4526241E1.

Re: Exporting to Excel converts text to an exponential number

Reeza — Mon, 18 Nov 2019 01:56:13 GMT

@MarkODwyer wrote:

Then from the 'Results' tab I select 'Send To', "Microsoft Excel'. There is no option that i can see to 'Send To' a textfile.

It's the Send to option that's causing the issue.

If you can instead use PROC EXPORT you may be able to avoid the issue.

Re: Exporting to Excel converts text to an exponential number

Kurt_Bremser — Tue, 19 Nov 2019 07:07:37 GMT

@MarkODwyer wrote:

Thanks Kurt,

Just to be clear, I am creating a dataset from the Query Builder. Then from the 'Results' tab I select 'Send To', "Microsoft Excel'. There is no option that i can see to 'Send To' a textfile.

Ouch. "Send to" is the least performant and most error-prone way to export data to Excel. It literally starts up Excel in the background and "types" in the data cell by cell. Use the Export task instead, or proc export in code. If you create a file directly in code, you may need to download it to your desktop with the Copy Files task if your SAS runs on a remote server.

Re: Exporting to Excel converts text to an exponential number

MarkODwyer — Wed, 20 Nov 2019 02:34:16 GMT

Thanks Kurt

Yes my SAS runs on a remote server. The approach you referred to (create a file directly in code, you may need to download it to your desktop with the Copy Files task) worked.

For reference the steps to follow can be found here

https://blogs.sas.com/content/sasdummy/2013/05/20/export-and-download-any-file-from-sas-enterprise-guide/