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.
I found the answer to why my code worked as expected, but yours didn't.
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;
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
I found the answer to why my code worked as expected, but yours didn't.
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;
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;
What about COALESCE() instead?
ROUND(coalesce(PROFIT, 0) / coalesce(SALES, 0), 0.01)
But you still have to account for dividing by zero.
What about COALESCE() instead?
ROUND(coalesce(PROFIT, 0) / coalesce(SALES, 0), 0.01)
But you still have to account for dividing by zero.
No big deal as you can make use the DIVIDE function to properly handle the divisions by zero
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.
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.