Desktop productivity for business analysts and programmers

Odd function in SAS

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 6
Accepted Solution

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
Respected Advisor
Posts: 4,976

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.

View solution in original post


All Replies
Esteemed Advisor
Posts: 7,294

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;

Respected Advisor
Posts: 3,124

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

Esteemed Advisor
Posts: 7,294

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;

Respected Advisor
Posts: 4,976

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.

Esteemed Advisor
Posts: 7,294

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;

Respected Advisor
Posts: 4,976

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.

Esteemed Advisor
Posts: 7,294

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.

Respected Advisor
Posts: 4,976

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!

Esteemed Advisor
Posts: 7,294

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;

Respected Advisor
Posts: 4,976

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!

Esteemed Advisor
Posts: 7,294

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.

Respected Advisor
Posts: 3,775

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;

Esteemed Advisor
Posts: 7,294

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
Respected Advisor
Posts: 4,976

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.

Need further help from the community? Please ask a new question.

Discussion stats
  • 20 replies
  • 2662 views
  • 1 like
  • 6 in conversation