DATA Step, Macro, Functions and more

ROUND Function Returning Missing Values

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 12
Accepted Solution

ROUND Function Returning Missing Values

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. 


Accepted Solutions
Solution
‎02-10-2018 06:08 PM
PROC Star
Posts: 8,104

Re: ROUND Function Returning Missing Values

Posted in reply to GeorgeBonanza

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
 
 
 

View solution in original post


All Replies
PROC Star
Posts: 8,104

Re: ROUND Function Returning Missing Values

[ Edited ]
Posted in reply to GeorgeBonanza

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

 

Solution
‎02-10-2018 06:08 PM
PROC Star
Posts: 8,104

Re: ROUND Function Returning Missing Values

Posted in reply to GeorgeBonanza

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
 
 
 
Occasional Contributor
Posts: 12

Re: ROUND Function Returning Missing Values

Thanks for your help!
Esteemed Advisor
Posts: 5,392

Re: ROUND Function Returning Missing Values

[ Edited ]
Posted in reply to GeorgeBonanza

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;

 

PG
Occasional Contributor
Posts: 12

Re: ROUND Function Returning Missing Values

Using CASE prevented the note from printing to the log. Thanks for your help!
Super User
Posts: 22,823

Re: ROUND Function Returning Missing Values

Posted in reply to GeorgeBonanza

What about COALESCE() instead?

 

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

But you still have to account for dividing by zero.

Super User
Posts: 22,823

Re: ROUND Function Returning Missing Values

Posted in reply to GeorgeBonanza

What about COALESCE() instead?

 

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

But you still have to account for dividing by zero.

☑ This topic is solved.

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

Discussion stats
  • 7 replies
  • 197 views
  • 0 likes
  • 4 in conversation