topic Re: Removing Exponents while creating CSV file using ODS CSV in SAS Programming

Removing Exponents while creating CSV file using ODS CSV

DeepikaM — Wed, 04 Nov 2015 06:54:17 GMT

Hi, I am creating a semicolon seperated CSV file using ODS CSV. Numeric column which is phone number and 15 digits long are being displayed as exponents in CSV file(opening using MS Excel). Also I have one character column which holds numeric value which is 20-25 digits , even that column is displayed as exponents in CSV file(opening using MS Excel). Please find the code below which I am using: ods csv file="&FileLoc.&FileName..csv" options(delimiter=";"); Options missing=''; proc report data=INVOICE_NOVAT split='#' ls=255; columns ( PHONE_NUMBER USER_NAME USER_REFERENCE1 USER_REFERENCE2 USER_REFERENCE3 YEAR MONTH INVOICE_ID FEES_NET DOMESTIC_USAGE_NET); define PHONE_NUMBER/display 'Subscription' ; define USER_NAME/display 'User'; define USER_REFERENCE1/display 'User reference 1'; define USER_REFERENCE2/display 'User reference 2'; define USER_REFERENCE3/display 'User reference 3'; define YEAR/display 'Year'; define MONTH/display 'Month'; define INVOICE_ID/display 'Invoice Number' ; define FEES_NET/display 'Fees'; define DOMESTIC_USAGE_NET/display 'Domestic usage (speech/SMS/MMS/data)'; run; ods csv close; Examples: INVOICE_ID : 5504694476130 PHONE_NUMBER : 580000404271 Your help is highly appreciated.

Re: Removing Exponents while creating CSV file using ODS CSV

SASKiwi — Wed, 04 Nov 2015 08:39:24 GMT

If you open your CSV file in NOTEPAD you will see that it is perfectly OK. The problem is Excel is applying formatting to the CSV when you open it and sometimes it doesn't get it right.

You can fix the formatting in Excel by setting the data type on those columns as text. If that doesn't work then do a DATA IMPORT into Excel rather than just open the CSV and specify the data type of the long numeric columns as text.

Re: Removing Exponents while creating CSV file using ODS CSV

DeepikaM — Wed, 04 Nov 2015 09:55:52 GMT

Hi,

Yes thats correct.

But I want a solution which we can apply in SAS code which I gave above.

So that when the CSV file is created , we will be able to see the number without exponents(original value)

Is there any possible solution which can be applied in ODS CSV or PROC REPORT?

Thanks in Advance.

Re: Removing Exponents while creating CSV file using ODS CSV

andreas_lds — Wed, 04 Nov 2015 10:03:09 GMT

Try Google, paste "site:sas.com excel leading zeros" (without quotes) in the search-box.

Re: Removing Exponents while creating CSV file using ODS CSV

RW9 — Wed, 04 Nov 2015 10:14:52 GMT

You cannot do that with CSV. Simple put CSV is a text based format, that when opened in Excel, is parsed and interpreted by the Excel engine in its own way - another reason why you should not be using Excel for anything! If you have to open output in Excel, then you will need to change your output format to tagsets.excelxp and add in the specific foramts which Excel should apply to each column. You can find examples of this here:

https://support.sas.com/resources/papers/proceedings11/266-2011.pdf

As SASKiwi has already told you, the problem is Excel, not SAS or the CSV file, it is the engine that Excel uses to read the CSV file. If you open the CSV file in anything other than Excel you will see the file is correct (assuming your export code is correct).