Help using Base SAS procedures

using substring in proc sql

Reply
Super Contributor
Posts: 401

using substring in proc sql

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.

Super User
Posts: 19,815

Re: using substring in proc sql

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

Super Contributor
Posts: 401

Re: using substring in proc sql

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 ?

Super User
Posts: 19,815

Re: using substring in proc sql

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

Super Contributor
Posts: 401

Re: using substring in proc sql

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

Super User
Posts: 19,815

Re: using substring in proc sql

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

Trusted Advisor
Posts: 1,228

Re: using substring in proc sql

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

Super Contributor
Posts: 401

Re: using substring 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.


Trusted Advisor
Posts: 3,214

Re: using substring in proc sql

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

---->-- ja karman --<-----
Trusted Advisor
Posts: 1,228

Re: using substring in proc sql

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;

Super Contributor
Posts: 401

Re: using substring in proc sql

This is very helpful, thank you.

Ask a Question
Discussion stats
  • 10 replies
  • 1826 views
  • 0 likes
  • 4 in conversation