BookmarkSubscribeRSS Feed
pcfriendly
Calcite | Level 5

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

13 REPLIES 13
Ksharp
Super User

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
Calcite | Level 5

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
Super User

so what output do you want ?

pcfriendly
Calcite | Level 5

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
Quartz | Level 8

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
Calcite | Level 5

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
Super User

That is exactly what I am doing.

pcfriendly
Calcite | Level 5

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
Super User

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

pcfriendly
Calcite | Level 5

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
Fluorite | Level 6

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


Advanced Expression.jpg
ChrisHemedinger
Community Manager

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

It's time to register for SAS Innovate! Join your SAS user peers in Las Vegas on April 16-19 2024.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

SAS Enterprise Guide vs. SAS Studio

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 13 replies
  • 1657 views
  • 0 likes
  • 5 in conversation