SAS Data Integration Studio, DataFlux Data Management Studio, SAS/ACCESS, SAS Data Loader for Hadoop and others

selecting digits using proc sql

Accepted Solution Solved
Reply
Contributor
Posts: 25
Accepted Solution

selecting digits using proc sql

Hi,

In IBM DB2, we can select certain digits from left or right, I am wondering if I can use proc sql to achieve same functionality as:

RIGHT(DIGITS(test.Id_Number), 3)

Thanks,

Eric


Accepted Solutions
Solution
‎08-01-2012 12:59 PM
Super Contributor
Posts: 1,636

Re: selecting digits using proc sql

Posted in reply to Nehcour0420

Yes,it is a character variable. you can convert it to numeric:

data have;
input v $;
cards;
adf124a
3456pb
7g39b2
;

proc sql;
create table want as select input(substr(compress(v,,'kd'),length(compress(v,,'kd'))-2),3.) as new
   from have;
quit;
proc contents data=want;run;

View solution in original post


All Replies
Super Contributor
Posts: 1,636

Re: selecting digits using proc sql

Posted in reply to Nehcour0420

how about:

data have;
input v $;
cards;
adf124a
3456pb
7g39b2
;

proc sql;
select substr(compress(v,,'kd'),length(compress(v,,'kd'))-2)as new
   from have;
quit;

Linlin

Contributor
Posts: 25

Re: selecting digits using proc sql

Thanks Linlin,

If I understand right, the sub string is a char type and could not be used to compared with a numeric type, is that right?

Solution
‎08-01-2012 12:59 PM
Super Contributor
Posts: 1,636

Re: selecting digits using proc sql

Posted in reply to Nehcour0420

Yes,it is a character variable. you can convert it to numeric:

data have;
input v $;
cards;
adf124a
3456pb
7g39b2
;

proc sql;
create table want as select input(substr(compress(v,,'kd'),length(compress(v,,'kd'))-2),3.) as new
   from have;
quit;
proc contents data=want;run;

🔒 This topic is solved and locked.

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

Discussion stats
  • 3 replies
  • 778 views
  • 0 likes
  • 2 in conversation