topic Re: sql query in SAS Enterprise Guide

sql query

rajeshm — Tue, 07 Oct 2014 15:01:07 GMT

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

Re: sql query

stat_sas — Tue, 07 Oct 2014 15:11:27 GMT

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;

Re: sql query

Reeza — Tue, 07 Oct 2014 15:13:14 GMT

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;

Re: sql query

Jagadishkatam — Tue, 07 Oct 2014 15:44:40 GMT

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

Re: sql query

slchen — Tue, 07 Oct 2014 16:11:13 GMT

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;

Re: sql query

rajeshm — Wed, 08 Oct 2014 06:16:49 GMT

hi,

thanks.

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

Re: sql query

stat_sas — Wed, 08 Oct 2014 15:04:20 GMT

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.