## Odd function in SAS

Solved
Occasional Contributor
Posts: 6

# Odd function in SAS

Hi,

is there an equivalent function in enterprise guide to the odd function in excel? or a similar formula to round a number to nearest odd integer?

Thanks

Accepted Solutions
Solution
‎02-01-2012 09:21 AM
Super User
Posts: 6,933

## Re: Odd function in SAS

You guys beat me to it.  Here's the one-line version:

y = ifn( x >= 0, ceil(x) + (mod(ceil(x),2)=0), floor(x) - (mod(floor(x),2)=0));

Nothing like understanding the question when you try to work on an answer.

All Replies
Super User
Posts: 8,216

## Odd function in SAS

If you are on 9.2, or newer, there are a number of excel functions that are now availble in the FCMP library.  Try the following:

options cmplib = sashelp.slkwxl;

data test;

input x;

cards;

1

2

3

4

5

6

7

8

9

;

data want;

set test;

y=odd_slk(x);

run;

Of course, if you are not on 9.2 or newer, you can simply submit the code that the function uses:

data want;

set test;

y = int(x) + sign(mod(x,int(x)));

if ( mod(y,2) eq 0 ) then y + sign(x);

run;

Posts: 3,185

## Odd function in SAS

Art,

Thank you for let us know this new feature of SAS 9.2. I know nothing of Excel ODD() function. However, the function you have introduced  does not round to nearest odd number like OP suggested,  instead, it only ceiling up to the next odd number. Please see:

options cmplib = sashelp.slkwxl;

data have;

do i=1 by 0.25 to 10;

j=ifn(mod(floor(i),2)=0,floor(i)+1,floor(i));

k=odd_slk(i);

output;

end;

run;

proc print;run;

Thanks,

Haikuo

Super User
Posts: 8,216

## Odd function in SAS

Haikuo,

Agreed!  The function is definitely lacking a step.  I think that the following matches Excel for all numbers:

data want;

set test;

y=ifn(x eq 0,1,round(x));

y = int(y) + sign(mod(y,int(y)));

if ( mod(y,2) eq 0 ) then y + sign(y);

run;

Super User
Posts: 6,933

## Odd function in SAS

Art,

It looks like you still have to guard against some values.  If you start with x=0.25, for example, the MOD function will attempt division by zero.

Super User
Posts: 8,216

## Odd function in SAS

Astounding,

How about nesting two round functions.  Let me know if the following misses any cases:

data want;

set test;

y=ifn(x eq 0,1,round(round(x,.5)));

y = int(y) + sign(mod(y,int(y)));

if ( mod(y,2) eq 0 ) then y + sign(y);

run;

Super User
Posts: 6,933

## Odd function in SAS

Art,

The more I look, the more I question.  These statements would handle small values:

if x=0 then y=1;

else if abs(x) < 1 then y = sign(x);

But for other cases, if y has already been rounded to an integer, wouldn't this expression always be equal to 0:

sign(mod(y,int(y)))

I think there is still some work to be done here.

Super User
Posts: 8,216

## Odd function in SAS

Astounding,

I tested it with the following data:

data test;

input x;

cards;

-9

-8

-7

-6

-5

-4

-3

-2

-1.5

-1

-.5

-.25

0

.25

.5

1

1.5

2

3

4

5

6

7

8

9

;

data want;

set test;

y=ifn(x eq 0,1,round(round(x,.5)));

y = int(y) + sign(mod(y,int(y)));

if ( mod(y,2) eq 0 ) then y + sign(y);

run;

proc print;

run;

Which resulted as follows:

Obs      x       y

1    -9.00    -9

2    -8.00    -9

3    -7.00    -7

4    -6.00    -7

5    -5.00    -5

6    -4.00    -5

7    -3.00    -3

8    -2.00    -3

9    -1.50    -3

10    -1.00    -1

11    -0.50    -1

12    -0.25    -1

13     0.00     1

14     0.25     1

15     0.50     1

16     1.00     1

17     1.50     3

18     2.00     3

19     3.00     3

20     4.00     5

21     5.00     5

22     6.00     7

23     7.00     7

24     8.00     9

25     9.00     9

Which I believe are the same values one would obtain from the Excel odd function.

Super User
Posts: 6,933

## Odd function in SAS

Art,

So far, so good.  Please try two more tests.  First, use the same program but try adding 0.1 as an incoming value.

Then run all the data (including 0.1 plus all the other values) through a similar program, but eliminate the middle statement entirely:

y = int(y) + ...

Thanks!

Super User
Posts: 8,216

## Odd function in SAS

Astounding (and anyone else keeping up with this),

Definitely still need(ed) some more work.  Removing the middle statement didn't correct matters, but I expanded the test file and added a nested set of ifn functions.  Try the following:

data test;

input x;

format x 12.6;

cards;

-9

-8

-7

-6

-5

-4.000001

-4

-3

-2

-1.5

-1

-.5

-.25

-.1

-.000001

0

.000001

.1

.25

.5

1

1.5

2

3

4

4.000001

5

6

7

8

9

;

data want;

set test;

y=ifn(0<=x<=.5,1,

ifn(-.5<x<0,-1,

round(round(x,.5))));

y = int(y) + sign(mod(y,int(y)));

if ( mod(y,2) eq 0 ) then y + sign(y);

run;

Super User
Posts: 6,933

## Odd function in SAS

Art,

Definitely on the right track here.  I suggested removing that middle statement because it looks like it's not doing anything.  It always leaves Y unchanged.

The cases to handle are dwindling, but it looks like there is still one consideration.  I think the double-rounding will cause a problem for 1.3.  It looks like you'll get 3 instead of 1 as the result.  Going back to the single rounding will take care of that.

To my tired eyes, that should do it!

Super User
Posts: 8,216

## Re: Odd function in SAS

Astounding,

No and Yes!  According to the Excel function, 0 and anything greater than an odd integer should be rounded up to the next odd integer (thus 1.3 should result in 3), and any negative number that is not an odd integer should result in the next lower odd integer.

Yes, the middle line is no longer needed.

Thus the "final" code should be:

data want;

set test;

y=ifn(0<=x<=.5,1,

ifn(-.5<x<0,-1,

round(round(x,.5))));

if ( mod(y,2) eq 0 ) then y + sign(y);

run;

I haven't tested FriedEgg's fcmp solution yet.

Posts: 3,867

## Re: Odd function in SAS

If I understand correctly I think FLOOR for negative and CEIL for positive should be used instead of ROUND.

`select(sign(val));   when(1) do;      c   = ceilz(val);      odd = not modz(c,2);      r   = c + odd;      end;   when(-1) do;      c   = floorz(val);      odd = not modz(c,2);      r   = c - odd;      end;   when(0) r=1;   otherwise;   end;`
Super User
Posts: 8,216

## Re: Odd function in SAS

DataNull, I totally agree.  Using the round function incorrectly assigned small fraction odd numbers (e.g., 5.00001), while your suggested code handles it quite nicely.

Solution
‎02-01-2012 09:21 AM
Super User
Posts: 6,933

## Re: Odd function in SAS

You guys beat me to it.  Here's the one-line version:

y = ifn( x >= 0, ceil(x) + (mod(ceil(x),2)=0), floor(x) - (mod(floor(x),2)=0));

Nothing like understanding the question when you try to work on an answer.

🔒 This topic is solved and locked.