turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

Find a Community

- Home
- /
- BI
- /
- Enterprise Guide
- /
- Function to locate the last character in a string

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

07-01-2013 09:48 PM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

07-01-2013 10:17 PM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

07-01-2013 11:12 PM

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.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

07-01-2013 11:18 PM

so what output do you want ?

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

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'

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

07-01-2013 11:50 PM

Have you tried Ksharp's answer?

Another option

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

Barry

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

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.

Any suggests?

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

07-01-2013 11:57 PM

That is exactly what I am doing.

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

07-02-2013 12:02 AM

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

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.

data have;

input Score;

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

cards;

119

110

100

114

120

115

106

109

;

run;

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

07-02-2013 12:21 AM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

07-02-2013 12:40 AM

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' ??

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

07-02-2013 09:55 AM

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Highlight
- Email to a Friend
- Report Inappropriate Content

07-02-2013 10:14 AM

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