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.
You probably have some AR_NO that are not long enough. Check the maximum and minimum length of the field.
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 ?
well, what results do you expect when the length is less than 11, or 1 for example
As long as it has the 10 values in the field I need, otherwise the field is useless
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
Not sure, can we use cast function in proc sql?
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.
The question is an indication you have an inconsistent dwh. Try to have it solved by the dwh guys first.
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;
This is very helpful, thank you.
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
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.