BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.

Hi,

 

I am trying to import an excel(.xls,.xlsx,csv) one of the columns has more than 20 length long values, and after the import the data is displayed as

Values on the Excel:

100000000882336

 

Displayed value:

1E14
1.0000001E14

 

can you please suggest?

 

1 ACCEPTED SOLUTION

Accepted Solutions
SASKiwi
PROC Star

Please note you cannot store accurately more than about 16 digits in a SAS numeric variable, so you need to import your column as a character variable. In Excel define your column as a Text column then try re-importing.

View solution in original post

5 REPLIES 5
ballardw
Super User

Common discussion related to computer precision and storage and display of values.

 

Are these values actually numbers used in arithemetic or something like account numbers? If the later then import them to character.

 

The variable has a format which may currently be BEST12. which is the default for some things. You may try changing that to BEST20. and see if that helps.

DonnaLevin
Calcite | Level 5

In a data step format the variable with Z20.

 

syntax:  format variable_name Z20.;

SASKiwi
PROC Star

Please note you cannot store accurately more than about 16 digits in a SAS numeric variable, so you need to import your column as a character variable. In Excel define your column as a Text column then try re-importing.

santosh_pat69
Quartz | Level 8

Hi ,

 

Thank you !!!

 

I was able to do it by using the below,

libname myxls xlsx "D:/.....xlsx";

data csvdata(Drop=Len);

Length newvar $50.;

set myxls.sheet1; 

newvar=put(ID, 16.);

Len=length(newvar);

Rename Newvar=ID; 

run;

 

 

 

SASKiwi
PROC Star

Good to see you've got a solution, but you shouldn't have to do it in code. If changing the column to text in Excel doesn't work then use the Text to Columns feature by highlighting the ID column then select the Text to Columns option on the Data menu.  

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
  • 5 replies
  • 7318 views
  • 0 likes
  • 4 in conversation