using substring in proc sql

[podarum]

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.

[Reeza]

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

[podarum]

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]

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

[podarum]

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

[Reeza]

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]

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

[podarum]

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]

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

[stat_sas]

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]

This is very helpful, thank you.

[cal4gORl3ndU]

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