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_.
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.