ROUND Function Returning Missing Values

[GeorgeBonanza]

proc sql; create table WANT as
	select REGION, SUM(SALES) as SALES, SUM(PROFIT) as PROFIT,
		   IFN(CALCULATED SALES in (0,.) or CALCULATED PROFIT in (0,.), 0, ROUND(PROFIT / SALES, 0.01)) as MARGIN
	from HAVE
	group by 1;
quit;

I am trying to do a calculation and I thought the code I have would eliminate any missing values in the results since I am checking for missing values before I do the ROUND(PROFIT / SALES, 0.01) calculation.  However, when I go to the log, I see the following:

NOTE: Invalid (or missing) arguments to the ROUND function have caused the function to return a missing value.

I want to have zeroes in the output and not missing values and I thought the code I have would produce that but unfortunately it is not.

Any help understanding why would be appreciated.  Thanks in advance. 

[art297]

Not sure why that doesn't work as expected, but I think that the following does:

proc sql;
  create table WANT as
    select REGION, SUM(SALES) as SALES, SUM(PROFIT) as PROFIT,
        case
          when CALCULATED SALES gt 0 and CALCULATED PROFIT gt 0
           then ROUND(calculated PROFIT / calculated SALES, 0.01)
          else 0
        end as MARGIN
      from HAVE
        group by 1
  ;
quit;

Art, CEO, AnalystFinder.com

[art297 · Accepted solution]

I found the answer to why my code worked as expected, but yours didn't.

"When the IFN and IFC functions are called, SAS evaluates all of the arguments, even those which end up unused." (quote from: www.howles.com/saspapers/CC33.pdf ).

Since I first saw Howard's paper, I have always tried to imbed the lag function within an ifn or ifc function so that the lag functioned as expected.

However, in your case, that precise functionality was causing the notes in your log.

Also, you had a problem in that you didn't state your ifn condition so that it had three possible results, namely true, false and missing. The following would still produce the annoying note, but at least would have worked as expected:

proc sql; create table WANT as
	select REGION, SUM(SALES) as SALES, SUM(PROFIT) as PROFIT,
		   IFN(CALCULATED SALES+CALCULATED PROFIT,
		     ROUND(calculated PROFIT / calculated SALES, 0.01),
		     0,
		     0) as MARGIN
	from HAVE
	group by 1;
quit;

Art, CEO, AnalystFinder.com

[GeorgeBonanza]

Thanks for your help!

[PGStats]

All three IFN arguments are evaluated, irrespective of the value of the condition. The case when else end construct does a better job:

proc sql;

select

REGION,

SUM(SALES) as SALES,

SUM(PROFIT) as PROFIT,

case when CALCULATED SALES in (0,.) or CALCULATED PROFIT in (0,.) then 0

else ROUND(calculated PROFIT / calculated SALES, 0.01) end as MARGIN

from HAVE

group by region;

quit;

[GeorgeBonanza]

Using CASE prevented the note from printing to the log. Thanks for your help!

[Reeza]

What about COALESCE() instead?

ROUND(coalesce(PROFIT, 0)  / coalesce(SALES, 0), 0.01)

But you still have to account for dividing by zero.

[Reeza]

What about COALESCE() instead?

ROUND(coalesce(PROFIT, 0)  / coalesce(SALES, 0), 0.01)

But you still have to account for dividing by zero.

[Florent]

No big deal as you can make use the DIVIDE function to properly handle the divisions by zero