Hi,
I want to create some calculated items like Non Operational Department Expenses (A&G + Sales) Below is the data structure
Date | Days | Department | Revenue | Budget | Expenses |
1-Jan | 31 | Rooms | 1,448,509 | 402393 | |
1-Feb | 28 | Rooms | 986,007 | 402441 | |
1-Jan | 31 | F&B | 556,213 | 312441 | |
1-Feb | 28 | F&B | 677,819 | 382441 | |
1-Jan | 31 | Telephone | 39,305 | 15000 | |
1-Feb | 28 | Telephone | 10,100 | 14321 | |
1-Jan | 31 | A&G | 126,525 | ||
1-Feb | 28 | A&G | 125,578 | ||
1-Jan | 31 | Sales | 70,894 | ||
1-Feb | 28 | Sales | 47,637 |
When I try to calculate "Non Operational Department Expenses" and use "Sum" on Expenses column it does a sum of all the departments and doesn't give me any option to do SUM of Expenses column for only A&G + Sales Departments. How can I just do a sum of expenses of these two departments?
You can try SUM function with CASE expression. For example: Using CLASS dataset to SUM Height for all 'M' gender.
proc sql noprint;
create view TEMP_LASR_VIEW_0 as
SELECT
CLASS.Name length=8 format=$8. AS Name,
CLASS.Sex length=1 format=$1. AS Sex,
CLASS.Age length=8 format=BEST12. AS Age,
CLASS.Height length=8 format=BEST12. AS Height,
CLASS.Weight length=8 format=BEST12. AS Weight,
CASE
WHEN Sex='M' then SUM(CASE
WHEN Sex='M' then Height
ELSE .
END )
ELSE .
END length=8 format=BEST12. AS Height_M
FROM
LASRLIB.CLASS CLASS;
quit;
Try creating an aggregated measure summed by - _BY_GROUP_.
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.
See how to use one filter for multiple data sources by mapping your data from SAS’ Alexandria McCall.
Find more tutorials on the SAS Users YouTube channel.