topic Odd function in SAS in SAS Enterprise Guide

Odd function in SAS

PJorge — Tue, 31 Jan 2012 18:35:22 GMT

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

Odd function in SAS

art297 — Tue, 31 Jan 2012 19:16:55 GMT

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;

;

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;

Odd function in SAS

Astounding — Tue, 31 Jan 2012 19:21:46 GMT

There's no ODD function that I know of, but this approach may work.

lower_odd = floor(original_number);

if mod(lower_odd,2) = 0 then lower_odd = lower_odd - 1;

if original_number - lower_odd < 1 then closest_odd = lower_odd;

else closest_odd = lower_odd + 2;

I use base SAS, hardly ever in EG. So I don't know if you'll need to adapt this or not. And you'll have to set up rules for ties. What if the original number is 4? What if it is -4? All of that can be coded though.

Good luck.

Odd function in SAS

Haikuo — Tue, 31 Jan 2012 19:33:48 GMT

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

Odd function in SAS

art297 — Tue, 31 Jan 2012 19:49:39 GMT

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;

Odd function in SAS

Astounding — Tue, 31 Jan 2012 19:55:49 GMT

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.

Odd function in SAS

art297 — Tue, 31 Jan 2012 20:15:54 GMT

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;

Odd function in SAS

Astounding — Tue, 31 Jan 2012 21:00:22 GMT

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.

Odd function in SAS

art297 — Tue, 31 Jan 2012 21:12:08 GMT

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

.25

1.5

;

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.

Odd function in SAS

Astounding — Tue, 31 Jan 2012 21:26:26 GMT

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!

Odd function in SAS

art297 — Tue, 31 Jan 2012 21:47:12 GMT

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

.000001

.25

1.5

4.000001

;

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;

Odd function in SAS

Astounding — Tue, 31 Jan 2012 21:59:07 GMT

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!

Re: Odd function in SAS

FriedEgg — Tue, 31 Jan 2012 22:29:25 GMT

This matches the output from Excel for the odd function using all Art's examples:

procfcmp;

function excel_odd(n);

return(ifn(n ge 0,ceil(n)+(mod(ceil(n),2)=0),floor(n)-(mod(floor(n),2)=0)));

endsub;

array x[31](-9 -8-7 -6-5 -4.00001-4 -3-2 -1.5-1 -.5-.25 -.1-.00001 0.00001 .1.25 .51 1.52 34 4.000015 67 89);

do i=1to dim(x);

excel_odd=excel_odd(x);

put excel_odd=;

end;

run;

Re: Odd function in SAS

art297 — Tue, 31 Jan 2012 22:34:48 GMT

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.

Re: Odd function in SAS

data_null__ — Wed, 01 Feb 2012 11:35:42 GMT

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;

Re: Odd function in SAS

art297 — Wed, 01 Feb 2012 13:34:36 GMT

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.

Re: Odd function in SAS

Astounding — Wed, 01 Feb 2012 14:21:33 GMT

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.

Re: Odd function in SAS

art297 — Wed, 01 Feb 2012 14:37:19 GMT

Astounding,

In all fairness, FriedEgg's and DataNull's proposed solutions also appear to work perfectly. However, I *really* like your one liner.

In case I've never mentioned it here, I'm a psychologist/director, not a programmer. The role I think I play here, and used to do on SAS-L, is to help refine interesting looking questions and, at the same time, motivate others to show the rest of us how it should/can be done.

Re: Odd function in SAS

PJorge — Thu, 02 Feb 2012 11:10:53 GMT

Just want to let you know that I got the results I wanted. All of your input were amazing and really helped me.

Thank you all

PS- I used the one line formula suggested by Astounding

Re: Odd function in SAS

art297 — Thu, 02 Feb 2012 13:24:33 GMT

Then mark his/her answer as being correct!