sql query

[rajeshm]

I want to fetch variable values ,if first letter of variable value is digit then that digit should be omitted and want to fetch values.

varible:

ram

lakshman

manhor1

1hello

2hai

fetched varible:

ram

lakshman

manohar1

hello

hai

[stat_sas · Accepted solution]

proc sql;

create table want as

select case when input(substr(name,1,1),8.)>=0 then compress(name,,'ka') else name end as fetched_name

from have;

quit;

[Reeza]

If this is in EG Query builder you'll need an advanced expression.

Substr out the first character and use the anydigit function 

If this was a data step:

data want;

set have;

first_char=substr(variable, 1,1);

if anydigit(first_char) then fetched_variable=substr(variable, 2);

else fetched_variable=variable;

run;

[Jagadishkatam]

Please try PRX

proc sql;

create table want as select variable,

case when prxmatch('/\d{1}/',variable)^=1 then variable

else  compress(variable,,'ka') end as variable2 from have;

quit;

Thanks,

Jag

[slchen]

data want;

set have;

fetched_variable=prxchange('s/^\d{1}(.)/$1/',-1,variable);

run;

proc sql;

select prxchange('s/^\d{1}(.)/$1/',-1,variable) as fetched_variable from have;

quit;

[rajeshm]

hi,

thanks.

instead of the input(substr(name,1,1),8.)>=0,we  can use notdigit(substr(name,1,1)) --easy to understand .

[stat_sas]

Hi,

Yes, this is a better way to do that. I think notalpha(substr(name,1,1)) will be used instead of notdigit(substr(name,1,1)) to get the desired results.