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:
With this information, I am trying to group the percentage of Beverage and Food sales into the following defined categories:
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
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
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
Thank you PG !
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.
What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.
Find more tutorials on the SAS Users YouTube channel.