BookmarkSubscribeRSS Feed
jayakumarmm
Quartz | Level 8

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
4 REPLIES 4
ballardw
Super User

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
Barite | Level 11
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
Super User

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
PROC Star

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.

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
  • 3873 views
  • 0 likes
  • 5 in conversation