Exporting to Excel converts text to an exponential number

[MarkODwyer]

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?

[ballardw]

Have you tried making the column wider in Excel?

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

[Reeza]

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?

---

[Kurt_Bremser]

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.

[MarkODwyer]

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.   

[SASKiwi]

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

[MarkODwyer]

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. 

[Reeza]

---

@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. 

[Kurt_Bremser]

---

@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.

[MarkODwyer]

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-g...