topic Re: ROUND Function Returning Missing Values in SAS Programming

ROUND Function Returning Missing Values

GeorgeBonanza — Sat, 10 Feb 2018 17:43:03 GMT

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.

Re: ROUND Function Returning Missing Values

art297 — Sat, 10 Feb 2018 18:57:23 GMT

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

Re: ROUND Function Returning Missing Values

art297 — Sat, 10 Feb 2018 20:12:10 GMT

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

Re: ROUND Function Returning Missing Values

PGStats — Sat, 10 Feb 2018 22:31:23 GMT

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;

Re: ROUND Function Returning Missing Values

Reeza — Sat, 10 Feb 2018 23:05:42 GMT

What about COALESCE() instead?

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

But you still have to account for dividing by zero.

Re: ROUND Function Returning Missing Values

Reeza — Sat, 10 Feb 2018 23:05:45 GMT

What about COALESCE() instead?

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

But you still have to account for dividing by zero.

Re: ROUND Function Returning Missing Values

GeorgeBonanza — Sat, 10 Feb 2018 23:10:29 GMT

Thanks for your help!

Re: ROUND Function Returning Missing Values

GeorgeBonanza — Sat, 10 Feb 2018 23:11:15 GMT

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

Re: ROUND Function Returning Missing Values

Florent — Wed, 26 Sep 2018 13:46:21 GMT

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