topic Re: SAS-EG Column Format in SAS Enterprise Guide

SAS-EG Column Format

RandyW — Wed, 10 Oct 2018 17:28:14 GMT

We have a column titled "Unique_Identifier" that is typically 3 characters long and contains numbers and letters. The column is a TEXT column in order to accommodate the number/letter combinations. The problem we are facing is when we export "as a step in the project", the Excel file is having trouble with the values that contain the letter E (ex: '0E4'). It thinks that value is an exponential number. Further more, when uploading to Power BI, our example of '0E4' is shown as _x0030_E4. We get the odd formatting if we use the file directly from the SAS export, but the weird part is if we save the excel file, then upload to Power BI, '0E4' correctly shows as '0E4'.

Any thoughts are appreciated. Thanks in advance!

Re: SAS-EG Column Format

Reeza — Wed, 10 Oct 2018 17:34:49 GMT

Excel is causing the issue here, not SAS - but changing your method of Export should help. If using ODS EXCEL you can use tagattr to force the column to be displayed as text.

@RandyW wrote:

We have a column titled "Unique_Identifier" that is typically 3 characters long and contains numbers and letters. The column is a TEXT column in order to accommodate the number/letter combinations. The problem we are facing is when we export "as a step in the project", the Excel file is having trouble with the values that contain the letter E (ex: '0E4'). It thinks that value is an exponential number. Further more, when uploading to Power BI, our example of '0E4' is shown as _x0030_E4. We get the odd formatting if we use the file directly from the SAS export, but the weird part is if we save the excel file, then upload to Power BI, '0E4' correctly shows as '0E4'.

Any thoughts are appreciated. Thanks in advance!

Re: SAS-EG Column Format

SuryaKiran — Wed, 10 Oct 2018 18:00:31 GMT

How are you exporting the data into Excel from SAS.

Re: SAS-EG Column Format

RandyW — Wed, 10 Oct 2018 18:43:47 GMT

Exporting as a step in the project

Re: SAS-EG Column Format

Reeza — Wed, 10 Oct 2018 18:52:32 GMT

I think that actually creates an HTML file that SAS can open, but I may be mistaken. Try explicitly exporting it as PROC EXPORT or using ODS EXCEL instead.

Re: SAS-EG Column Format

RandyW — Wed, 10 Oct 2018 19:01:27 GMT

Thanks for all the quick responses. For some reason changing the output file type to .CSV (instead of .XLSX) works for us.

We will try the ODS EXCEL suggestion also.