BookmarkSubscribeRSS Feed
podarum
Quartz | Level 8

Hi,

I'm tring to substring a character field (eg. 00000000012345678912) in proc sql and and need to convert it to numeric.  I only need the 1234567891 from the field (position 10 for 11 fields).  I'm using this format :

cast((substr( AR_NO,10,11)) as integer) as AR_NO_SUB , but getting an error (below)

ERROR: Teradata row not delivered (trget): Overflow occurred computing an expression involving DB.AR_NO.

if I do cast((substr( AR_NO,4,11)) as integer) as AR_NO_SUB i get 69267, but

if I do cast((substr( AR_NO,5,11)) as integer) as AR_NO_CUB I get the error as above in red.

Thankss for your help.

11 REPLIES 11
Reeza
Super User

You probably have some AR_NO that are not long enough. Check the maximum and minimum length of the field.

podarum
Quartz | Level 8

If this is the correct way of doing it

max(length(a.AR_NO)) as max, min(length(a.AR_NO)) as min, then the results are 20 max and 1 min.. Knowing this info, is there a fix to get this working ?

Reeza
Super User

well, what results do you expect when the length is less than 11, or 1 for example Smiley Happy

podarum
Quartz | Level 8

As long as it has the 10 values in the field I need, otherwise the field is useless

Reeza
Super User

My question would be why is the length less than what you expect.

To deal with it from a programming perspective, you can use a case statement.

case when length(ar_no)>20 then cast ....

else Null end as new_var

stat_sas
Ammonite | Level 13

Not sure, can we use cast function in proc sql?

podarum
Quartz | Level 8

Good question.. but I guess I should of mentioned that I need to connect to Teradata first, since it is data from the Enterprise Datawarehouse. So this I'm actually doing in Teradata SQL embedded in proc sql.


jakarman
Barite | Level 11

The question is an indication you have an inconsistent dwh.  Try to have it solved by the dwh guys first.

---->-- ja karman --<-----
stat_sas
Ammonite | Level 13

Not sure if this can solve the problem.

proc sql;

select AR_NO,input(substr( AR_NO,10,11),best.) as integer as AR_NO_SUB from have;

quit;

podarum
Quartz | Level 8

This is very helpful, thank you.

cal4gORl3ndU
Calcite | Level 5

If you are using Teradata and need to convert an attribute that is a character-type to a numeric-type, let me suggest that you do the work using "explicit SQL" in the native Teradata SQL dilect, and not in SAS-SQL.

 

The example below shows the steps I use.  Only the last is truly needed.  The regex replace function removed any remaining non-numeric characters, like any potential nonprinting characters.

 

SELECT
    '00000000012345678912'
    , TRIM( LEADING '0' FROM '00000000012345678912' )
    , REGEXP_REPLACE( TRIM( LEADING '0' FROM '00000000012345678912' ), '[^0-9]+','',1,0,'i')
    , CAST( REGEXP_REPLACE( TRIM( LEADING '0' FROM '00000000012345678912' ), '[^0-9]+','',1,0,'i') AS BIGINT )
;

 

You may be able to use the INTEGER datatype. Instead of a BIGINT.

 

The number in your example (12,345,678,912) is a BIGINT, a 64-bit (8-byte) signed integer.
Range: -9,223,372,036,854,775,808 to 9,223,372,036,854,775,807

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!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

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