07-01-2013 09:48 PM
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
07-01-2013 11:30 PM
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'
07-01-2013 11:58 PM
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.
07-02-2013 12:03 AM
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.
if mod(score,10)=9 then score=score+1;
07-02-2013 12:40 AM
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' ??
07-02-2013 09:55 AM
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;
I am not sure if you are looking for the same or not.
07-02-2013 10:14 AM
create table fix as
/* BEGIN EXP */ ifn( mod(num,10) = 9,num+1,num) /* END EXP */