topic Re: Function to locate the last character in a string in SAS Enterprise Guide

Function to locate the last character in a string

pcfriendly — Tue, 02 Jul 2013 01:48:50 GMT

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

Re: Function to locate the last character in a string

Ksharp — Tue, 02 Jul 2013 02:17:47 GMT

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

Re: Function to locate the last character in a string

pcfriendly — Tue, 02 Jul 2013 03:12:13 GMT

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.

Re: Function to locate the last character in a string

Ksharp — Tue, 02 Jul 2013 03:18:39 GMT

so what output do you want ?

Re: Function to locate the last character in a string

pcfriendly — Tue, 02 Jul 2013 03:30:51 GMT

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'

Re: Function to locate the last character in a string

twocanbazza — Tue, 02 Jul 2013 03:50:04 GMT

Have you tried Ksharp's answer?

Another option

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

Barry

Re: Function to locate the last character in a string

Ksharp — Tue, 02 Jul 2013 03:57:46 GMT

That is exactly what I am doing.

Re: Function to locate the last character in a string

pcfriendly — Tue, 02 Jul 2013 03:58:28 GMT

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?

Re: Function to locate the last character in a string

twocanbazza — Tue, 02 Jul 2013 04:02:16 GMT

Re: Function to locate the last character in a string

pcfriendly — Tue, 02 Jul 2013 04:03:37 GMT

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;

Re: Function to locate the last character in a string

Ksharp — Tue, 02 Jul 2013 04:21:55 GMT

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

Re: Function to locate the last character in a string

pcfriendly — Tue, 02 Jul 2013 04:40:16 GMT

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

Re: Function to locate the last character in a string

pali — Tue, 02 Jul 2013 13:55:53 GMT

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

Re: Function to locate the last character in a string

ChrisHemedinger — Tue, 02 Jul 2013 14:14:16 GMT

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;

;

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