Excel import - Exponential value into SAS as character field

[jayakumarmm]

I am trying to import a excel (.xlsx file) using proc import but the problem facing with the column values more than 16 digits in text format. 

During import 16 digits value is converted into exponential character variable into SAS which I am able to convert into standard numerical value. Please assist.

Sample Excel data:

1202220022121121

SAS Variable data

1.2E+15

[ballardw]

How did you bring the Excel data into SAS?

Also it is very likely that the value SAS has is actually numeric but the default Display format is 8 and the only way SAS can display the value with a display limit of 8 characters (including a - sign that may not be present) is in exponential form.

Try changing the format to BEST16. and see if that is sufficient.

You can change the format at display time in proc print, report or similar by specifying the format, or click on the table view column header and set the property.

If the purpose of the variable is an identifier such as an account number or phone number then it really should not be numeric as you are not going to do arithmetic with it. And you may have lost significant leading zeroes at some point.

[rogerjdeangelis]

Casting 15 digit excel numbers to character using passthru

HAVE ( Where X is numeric)

  +------------------+
  |        A         |
--+------------------+
1 | X                |
--|------------------+
2 |  1202220022121120|
---------------------+
3 |  1202220022121120|
---------------------+
4 |  1202220022121120|
--+------------------+

num18

WANT  (note the sheet name is num18 could be the default)
====

Up to 40 obs WORK.XLS_CAST total obs=3

Obs         CHRNUM

 1     1202220022121120
 2     1202220022121120
 3     1202220022121120

WORKING CODE

 format(X,'################') as ChrNum


FULL SOLUTION
=============

* create a sheet with the 15 digit numbers;

%utlfkil(d:/xls/utl_excel_cast.xlsx);
libname xel "d:/xls/utl_excel_cast.xlsx";
data xel.num18;
  do x=1202220022121121,1202220022121121,1202220022121121;
     output;
  end;
run;quit;
libname xel clear;

* cast the numbers to char using passthru;
proc sql dquote=ansi;
  connect to excel (Path="d:\xls\utl_excel_cast.xlsx" mixed=yes);
    create table xls_cast as
    select
        ChrNum     length=16
        from connection to Excel
        (
         Select
            format(X,'################') as ChrNum
         from
           num18
        );
    disconnect from Excel;
Quit;

[Reeza]

SAS can't represent numbers with 16+ digits accurately, yes, this sucks, but its a bit of computer restriction more than SAS.

If you create a CSV file with a 16 digit number and bring it into Excel, it will mess up the number and doesn't tell you either. It just rounds the last 4 digits to zero.

Anyways, yes, you need to force SAS to read this field as character. Look into the DBSASTYPE option to be able to import it as a character. 

Numerical Precision in SAS

http://support.sas.com/documentation/cdl/en/lrcon/68089/HTML/default/viewer.htm#p0ji1unv6thm0dn1gp4t...

[SASKiwi]

Another option is use the Data menu in Excel to do a Text to Column conversion on the numeric column prior to importing (ensure you select column type Text during in the Wizard). If you do that SAS will read the column as character.