Proc SQL Substring of a numeric variable

[This widget could not be displayed.]

Hi,

Looking to include a substring on my query but it wont work and I get this error message -

ERROR: Function SUBSTR requires a character expression as argument 1.

Code -

proc sql;
create table work.risk as
select
SUBSTR(account_number, 2, 9) as acc,
from
rmort.c201410_new;
quit;

[Kurt_Bremser]

account_number seems to be numeric.

Try substr(put(account_number,z10.),2,9)

[AminB]

SAS doen't seem to think like humans with their PUT and INPUT notation

[Afor910327]

Hi Kurt,

I am having the same error, different query, do you know what is wrong with it?

MPRINT(SERIES): data tempsas.GDPxInd_pub_total_emp;
MPRINT(SERIES): set tempsas.Published_GDPxInd_Employment;
MPRINT(SERIES): where trim(left(line)) = "2";
ERROR: Function LEFT requires a character expression as argument 1.
SYMBOLGEN: Macro variable IOYEAR resolves to 2014
MPRINT(SERIES): BEA_Pub_Total_Emp = y2014;
MPRINT(SERIES): keep BEA_Pub_Total_Emp;
MPRINT(SERIES): run;

Thanks!

[RamKumar]

SUBSTR is valid only for character variables NOT for numeric. Try with SUBSTRN function like below for numeric variables.

data have;
a=1234567;
run;

data want;
set have;
new_a=substrn(a,3,2);
run;

[Ksharp]

If I guess right your account_number is numeric variable.

SUBSTR(put(account_number,best32. -L), 2, 9) as acc,

[MikeZdeb · Accepted solution]

---

@Ksharp wrote:

If I guess right your account_number is numeric variable.

SUBSTR(put(account_number,best32. -L), 2, 9) as acc,

Hi.  If Xia is correct in that ACCOUNT_NUMBER is numeric, you could also try a CAT function to temporarily use ACCOUNT_NUMBER as a character variable ...

data have;

input account_number;

datalines;

1

12

123

1234

12345

123456

1234567

12345678

123456789

1234567890

;

proc sql;

create table want as

select substr(cat(account_number), 2, 9) as acc

from have;

quit;

Obs    acc

  1

  2    2

  3    23

  4    234

  5    2345

  6    23456

  7    234567

  8    2345678

  9    23456789

10    234567890