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

Hello,

I want to append into Oracle a record that contains a numeric value from SAS.

The value in SAS is defined as NUMERIC(8) with a format/informat of 19.2. It's an amount field, so it contains value like 120.14.

In Oracle, the field is defined as NUMBER(19, 2). When I view the table in SAS, it says it's NUMERIC(8) with a format/informat of 21.2.

 

When I try the append, I have the following error:
ERROR: ERROR: ERROR: ERROR: ORACLE execute error: ORA-01438: value larger than specified precision allowed for this column.
With the occurrence of the above ERROR, the error limit of 1 set by the ERRLIMIT= option has been reached. ROLLBACK has been
issued(Any Rows processed after the last COMMIT are lost). Total rows processed: 1 Rows failed : 1
ROLLBACK has been issued(Any Rows processed after the last COMMIT are lost).

 

I understand the error. It means I'm trying to pass a SAS value that is bigger than what Oracle would allow. I don't know how exactly to correct this in this case. Should I alter the Oracle table to allow bigger values? Should I change the SAS format?

Any help would be appreciated.

1 ACCEPTED SOLUTION

Accepted Solutions
Kaladhan
Fluorite | Level 6

... I identified the wrong field as the problem. Another field was defined too small.

View solution in original post

2 REPLIES 2
Tom
Super User Tom
Super User

SAS stores numbers as 64 bit floating point numbers. There are not enough bits to store 19 decimal digits. But since it it floating point you can easily store a number that is larger than 10**19.

Find the maximum value in the data you are trying to store and see if that is the issue.

 

I guess another possibility is that you are trying to store too many decimal digits?  Perhaps your value is somthing like 12.125 and Oracle is complainging that has no place to store the 5?  If that is the problem then you could try rounding the value to 2 decimal places.  Formats just impact how it is display, the ROUND() function will change the actual value.

Kaladhan
Fluorite | Level 6

... I identified the wrong field as the problem. Another field was defined too small.

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
  • 2 replies
  • 29412 views
  • 0 likes
  • 2 in conversation