11-16-2016 04:10 PM
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:
SAS Variable data
11-16-2016 04:30 PM
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.
11-16-2016 05:15 PM
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;
11-16-2016 05:25 PM
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
11-17-2016 01:18 AM
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.