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
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
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.
so what output do you want ?
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'
Have you tried Ksharp's answer?
Another option
Convert the number to a char and then use the similar logic from your excel
Barry
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?
That is exactly what I am doing.
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;
score=ifn(mod(score,10)=9,score+1,score)
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' ??
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
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
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.
Find more tutorials on the SAS Users YouTube channel.