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 !
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.