BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
GeorgeBonanza
Obsidian | Level 7
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. 

1 ACCEPTED SOLUTION

Accepted Solutions
art297
Opal | Level 21

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

8 REPLIES 8
art297
Opal | Level 21

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
Opal | Level 21

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
Obsidian | Level 7
Thanks for your help!
PGStats
Opal | Level 21

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
GeorgeBonanza
Obsidian | Level 7
Using CASE prevented the note from printing to the log. Thanks for your help!
Reeza
Super User

What about COALESCE() instead?

 

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

But you still have to account for dividing by zero.

Reeza
Super User

What about COALESCE() instead?

 

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

But you still have to account for dividing by zero.

Florent
Quartz | Level 8

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 8 replies
  • 4130 views
  • 0 likes
  • 5 in conversation