Function to locate the last character in a string

[pcfriendly]

Hi,

I have a number field & some values end in a '9' & I want to round those to the next number - that is '29' becomes '30'.

I know I could use a 'Round' type function but I'd like to know how I can firstly locate the last integer from a number value & if it is a '9' add 1 to it, otherwise leave the value as it is.

In excel I would add this - =IF(RIGHT(E2,1)="9",E2+1,E2).

I looked at the RIGHT, SUBSTR & SUBSTRN functions but I suppose they are for text??

Can anyone suggest the best answer?  Thanks

[Ksharp]

What does your data look like ?

data have;
input Score;
if mod(score,10)=9 then score=score+1;
cards;
119
110
100
114
120
115
106
109
;
run;

Ksharp

[pcfriendly]

Hi Ksharp,

The Field contains only Integers such as 0, 29, 30, 59, 150, 99 etc, so I want the ones that end in '0' to be left alone.

[Ksharp]

so what output do you want ?

[pcfriendly]

As advised in the question;

I'd like to know how I can firstly locate the last integer from a number value & if it is a '9' add 1 to it, otherwise leave the value as it is. '29' becomes '30'

[twocanbazza]

Have you tried Ksharp's answer?

Another option

Convert the number to a char and then use the similar logic from your excel

Barry

[pcfriendly]

Hi Barry Stevens,

No I haven't tried KSharps answer as I need it as an advanced expression - such as the one I posted as what Excel would accept.

I should have explained that, so I'm looking for someting I can add into the Advanced Expression area of SAS EG 4.3.

I didn't mean to be rude KSharp, I also answer Forum questions & if someting is obvious, I reuse what I've already written.

I think I did explain the bit about strings & numbers clearly enough, I don't know what function to use & these (String) ones didn't work, so I'm asking what function is best here.

Any suggests?

[Ksharp]

That is exactly what I am doing.

[twocanbazza]

[pcfriendly]

Thanks, but if you mean the script your posted above, it's obviously not going to work in the Advanced Expression area of SAS EG 4.3.

data have;

input Score;

if mod(score,10)=9 then score=score+1;

cards;

119

110

100

114

120

115

106

109

;

run;

[Ksharp]

score=ifn(mod(score,10)=9,score+1,score)   

[pcfriendly]

Thanks Ksharp,

Unfortunately, this doesn't work.

to use it, I replaced the 'score' value with my field name & got the results of '0's & '1's instead of the field value + 1.

Existing values ending in '0' such as '40' were displayed as '1' & values ending in '9' such as '29' were displayed as '0' ??

[pali]

Hi,

In the query builder you can use expression like this to create a calculated column:

case when substr(compress(put(num,15.)), length(compress(put(num,15.))),1)='9' then num+1;

else num

end

I am not sure if you are looking for the same or not.

-Saurabh

[ChrisHemedinger]

I think has it.  When adding your expression in the Query Builder, don't include the initial "score =".  Here's an example in the context of PROC SQL, which is how the Query Builder will use it.

data test;

input num;

datalines;

45

89

77

9

45

0

;

proc sql;

create table fix as

  select num,

  /* BEGIN EXP */ ifn( mod(num,10) = 9,num+1,num) /* END EXP */

    as want

  from test;

quit;

Chris