SAS vs Excel data conversion

Accepted Solution Solved
Reply
Trusted Advisor
Posts: 1,228
Accepted Solution

SAS vs Excel data conversion

Hi Everyone,

I've created a dataset from sql server that contains id with 28 digits using dbsastype=(id='CHAR(28)').

While making csv file using proc export I am not getting same data values for id in csv file.

Any suggestion for this one please?

Regards,

Naeem


Accepted Solutions
Solution
‎02-27-2014 02:05 PM
Trusted Advisor
Posts: 3,212

Re: SAS vs Excel data conversion

It is an Intel issue not microsoft or SAS or any other program. There is even an IEE-standard.

IEEE floating point - Wikipedia, the free encyclopedia

floating point double-precision is what Intel is using as standard at the moment. Precision is 15 digits.

SAS(R) 9.2 Companion for Windows, Second Edition

Welcome in the digital age and the caveats.

"There are 10 types of people in the world; those that understand BINARY and those that do not"..  

---->-- ja karman --<-----

View solution in original post


All Replies
Super User
Posts: 19,780

Re: SAS vs Excel data conversion

How are you verifying the values of the ID in the CSV file? Through Excel?

Trusted Advisor
Posts: 1,228

Re: SAS vs Excel data conversion

Hi Reeza,

I am getting values like given below

2.01306E+26

while actual value is 201306071018396956500600003

I tried to convert these into numbers using number category of format cell in excel and getting this.

201306071018396000000000000
 

Super User
Posts: 19,780

Re: SAS vs Excel data conversion

Its an Excel issue, not SAS. Open the file with a text reader instead and see what was exported.

I think you can get around this by exporting a quoted comma delimited file, rather than straight CSV.

EDIT: Look into ODS CSV to get a quoted comma delimited file.

Valued Guide
Posts: 2,177

Re: SAS vs Excel data conversion

Open the csv file in excel with the (excel) import wizard. That will allow you to redefine the column from "general" to "text"

Solution
‎02-27-2014 02:05 PM
Trusted Advisor
Posts: 3,212

Re: SAS vs Excel data conversion

It is an Intel issue not microsoft or SAS or any other program. There is even an IEE-standard.

IEEE floating point - Wikipedia, the free encyclopedia

floating point double-precision is what Intel is using as standard at the moment. Precision is 15 digits.

SAS(R) 9.2 Companion for Windows, Second Edition

Welcome in the digital age and the caveats.

"There are 10 types of people in the world; those that understand BINARY and those that do not"..  

---->-- ja karman --<-----
Super User
Posts: 19,780

Re: SAS vs Excel data conversion

It's a char variable, not a number, so excel converting it to a number automatically is an Excel issue, in my opinion.

Valued Guide
Posts: 2,177

Re: SAS vs Excel data conversion

And excel provides a (manual) way to change the type when data is imported

Occasional Contributor
Posts: 8

Re: SAS vs Excel data conversion

Jaap is correct, but there is a work around if you are interested.

Open the csv file with notepad and save it as a .txt file.  Open excel and then open the .txt file.  The text import wizard will show up.  In step one make sure "Delimited" is checked.  In step 2, make sure comma is checked under delimiters.  In step 3, click on whatever column(s) have the long number and make sure that column data format for that column is selected as text.  Click finish and you have an excel doc with long numbers that are not truncated.

I believe this should work fine with proc export, but if it doesnt this is the way I normally use:

ods csvall file='C:\Location\FileName.csv';

proc print data=myfile;

run;

ods _all_  close;

ods listing;

Trusted Advisor
Posts: 1,228

Re: SAS vs Excel data conversion

Posted in reply to ewhulbert

Thanks ewhulbert - Excellent stuff.

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 9 replies
  • 598 views
  • 3 likes
  • 5 in conversation