Desktop productivity for business analysts and programmers

sql query

Accepted Solution Solved
Reply
Contributor
Posts: 44
Accepted Solution

sql query

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


Accepted Solutions
Solution
‎10-07-2014 11:11 AM
Trusted Advisor
Posts: 1,203

Re: sql query

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;

View solution in original post


All Replies
Solution
‎10-07-2014 11:11 AM
Trusted Advisor
Posts: 1,203

Re: sql query

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;

Grand Advisor
Posts: 17,396

Re: sql query

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;

Super User
Posts: 1,117

Re: sql query

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

Thanks,
Jag
Super Contributor
Posts: 275

Re: sql query

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;

Contributor
Posts: 44

Re: sql query

hi,

thanks.

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

Trusted Advisor
Posts: 1,203

Re: sql query

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.

☑ This topic is SOLVED.

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

Discussion stats
  • 6 replies
  • 462 views
  • 0 likes
  • 5 in conversation