Proc SQL Substring of a numeric variable

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 138
Accepted Solution

Proc SQL Substring of a numeric variable

[ Edited ]

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;


Accepted Solutions
Solution
‎10-17-2016 04:55 PM
Valued Guide
Posts: 763

Re: Proc SQL Substring of a numeric variable

[ Edited ]

@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

View solution in original post


All Replies
Esteemed Advisor
Posts: 6,695

Re: Proc SQL Substring

account_number seems to be numeric.

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

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Occasional Contributor
Posts: 6

Re: Proc SQL Substring

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

Occasional Contributor
Posts: 8

Re: Proc SQL Substring

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!

Regular Contributor
Posts: 165

Re: Proc SQL Substring

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;

Grand Advisor
Posts: 9,592

Re: Proc SQL Substring

If I guess right your account_number is numeric variable.

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

Solution
‎10-17-2016 04:55 PM
Valued Guide
Posts: 763

Re: Proc SQL Substring of a numeric variable

[ Edited ]

@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

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 6 replies
  • 16768 views
  • 7 likes
  • 7 in conversation