BookmarkSubscribeRSS Feed
DeepikaM
Calcite | Level 5
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.
4 REPLIES 4
SASKiwi
PROC Star

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.

DeepikaM
Calcite | Level 5

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.

andreas_lds
Jade | Level 19

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

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 4 replies
  • 1169 views
  • 0 likes
  • 4 in conversation