topic Re: Between (2 percentages) Function in Case Statement with Calculation in SAS Enterprise Guide

Between (2 percentages) Function in Case Statement with Calculation

chrisjab — Wed, 17 Aug 2016 20:34:06 GMT

Good Afternoon,

I am trying to create a case statement where the defined criteria are in percentages and the conditions are in Dollars; given the differences between the criteria and the defined conditions I am running into rounding issues resulting in transactions being left out.

For example, I have 3 columns identified as:

Sale_Beverage
Sale_Food
Total_ Sales

With this information, I am trying to group the percentage of Beverage and Food sales into the following defined categories:

If Total_Sales = $0 then "No Activity"
If (Sale_Food/ Total_Sales) >= 80% then "FoodH"
If (Sale_Food/ Total_Sales) between 55% and 79% then "FoodM"
If (Sale_Food/ Total_Sales) between 45% and 54% the "FoodL"
.....

Given the criteria this is what I came up with:

CASE
WHEN Total_Sales = 0
THEN "No Activity"
WHEN (Sale_Food/Total_Sales)>= 0.80 -- for 80%
THEN "FoodH"
WHEN (Sale_Food/Total_Sales) BETWEEN 0.55 and 0.79 -- for between 55% and 79%

THEN "FoodM"

...

END

Any help would be gladly appreciated.

Thanks,

Chris

Re: Between (2 percentages) Function in Case Statement with Calculation

PGStats — Wed, 17 Aug 2016 20:51:47 GMT

Use the fact that case clauses are tested in order until one matches. Make sure you cover all possibilities

CASE
WHEN Total_Sales = 0
	THEN "No Activity"
WHEN (Sale_Food/Total_Sales) < 0.45
	THEN "Food?"
WHEN (Sale_Food/Total_Sales) < 0.55
	THEN "FoodL"
WHEN (Sale_Food/Total_Sales) < 0.80
	THEN "FoodM"
	ELSE "FoodH"
END as Category

Re: Between (2 percentages) Function in Case Statement with Calculation

chrisjab — Wed, 17 Aug 2016 21:46:17 GMT

Thank you PG !