topic Proc SQL Substring of a numeric variable in SAS Programming

Proc SQL Substring of a numeric variable

anonymous_user — Mon, 17 Oct 2016 20:56:08 GMT

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;

Re: Proc SQL Substring

Kurt_Bremser — Thu, 13 Nov 2014 12:25:01 GMT

account_number seems to be numeric.

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

Re: Proc SQL Substring

RamKumar — Thu, 13 Nov 2014 12:30:26 GMT

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;

Re: Proc SQL Substring

Ksharp — Thu, 13 Nov 2014 12:36:20 GMT

If I guess right your account_number is numeric variable.

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

Re: Proc SQL Substring of a numeric variable

MikeZdeb — Mon, 17 Oct 2016 20:56:25 GMT

@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;

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

2 2

3 23

4 234

5 2345

6 23456

7 234567

8 2345678

9 23456789

10 234567890

Re: Proc SQL Substring

AminB — Thu, 13 Nov 2014 17:52:52 GMT

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

Re: Proc SQL Substring

Afor910327 — Tue, 27 Sep 2016 17:03:55 GMT

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!