Exploring, predicting and reporting with SAS Visual Analytics and SAS Visual Statistics

Calculating SUM for Expenses Column for specific department

Reply
New Contributor
Posts: 3

Calculating SUM for Expenses Column for specific department

[ Edited ]

Hi,

 

I want to create some calculated items like Non Operational Department Expenses (A&G + Sales)  Below is the data structure

 

DateDaysDepartmentRevenueBudgetExpenses
1-Jan31Rooms1,448,509 402393
1-Feb28Rooms986,007 402441
1-Jan31F&B556,213 312441
1-Feb28F&B677,819 382441
1-Jan31Telephone39,305 15000
1-Feb28Telephone10,100 14321
1-Jan31A&G  126,525
1-Feb28A&G  125,578
1-Jan31Sales  70,894
1-Feb28Sales  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?

 

 

PROC Star
Posts: 631

Re: Calculating SUM for Expenses Column for specific department

You can try SUM function with CASE expression. For example: Using CLASS dataset to SUM Height for all 'M' gender.Capture.PNGCapture.PNG

 


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;
Thanks,
Suryakiran
Super User
Posts: 4,025

Re: Calculating SUM for Expenses Column for specific department

Try creating an aggregated measure summed by  - _BY_GROUP_.

Ask a Question
Discussion stats
  • 2 replies
  • 137 views
  • 0 likes
  • 3 in conversation