Odd function in SAS

[PJorge]

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

[art297]

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;

[Haikuo]

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

[art297]

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;

[Astounding]

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.

[art297]

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;

[Astounding]

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.

[art297]

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.

[Astounding]

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!

[art297]

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;

[Astounding]

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!

[art297]

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.

[data_null__]

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;

[art297]

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.

[Astounding · Accepted solution]

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.