topic Re: using substring in proc sql in SAS Procedures

using substring in proc sql

podarum — Thu, 26 Jun 2014 16:09:27 GMT

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.

Re: using substring in proc sql

Reeza — Thu, 26 Jun 2014 16:13:54 GMT

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

Re: using substring in proc sql

stat_sas — Thu, 26 Jun 2014 16:36:53 GMT

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

Re: using substring in proc sql

podarum — Thu, 26 Jun 2014 16:42:52 GMT

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.

Re: using substring in proc sql

podarum — Thu, 26 Jun 2014 17:01:53 GMT

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 ?

Re: using substring in proc sql

Reeza — Thu, 26 Jun 2014 17:13:12 GMT

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

Re: using substring in proc sql

podarum — Thu, 26 Jun 2014 17:18:16 GMT

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

Re: using substring in proc sql

Reeza — Thu, 26 Jun 2014 17:20:09 GMT

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

Re: using substring in proc sql

jakarman — Thu, 26 Jun 2014 18:12:37 GMT

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

Re: using substring in proc sql

stat_sas — Thu, 26 Jun 2014 18:20:43 GMT

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;

Re: using substring in proc sql

podarum — Thu, 26 Jun 2014 18:34:02 GMT

This is very helpful, thank you.

Re: using substring in proc sql

cal4gORl3ndU — Tue, 09 Apr 2019 20:54:47 GMT

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