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;
@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
account_number seems to be numeric.
Try substr(put(account_number,z10.),2,9)
SAS doen't seem to think like humans with their PUT and INPUT notation
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!
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;
If I guess right your account_number is numeric variable.
SUBSTR(put(account_number,best32. -L), 2, 9) as acc,
@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
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.