topic Re: Calculating SUM for Expenses Column for specific department in SAS Visual Analytics

Calculating SUM for Expenses Column for specific department

arkam — Thu, 12 Apr 2018 17:31:17 GMT

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?

Re: Calculating SUM for Expenses Column for specific department

SuryaKiran — Thu, 12 Apr 2018 18:48:25 GMT

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;

Re: Calculating SUM for Expenses Column for specific department

SASKiwi — Sat, 14 Apr 2018 23:19:05 GMT

Try creating an aggregated measure summed by - _BY_GROUP_.