BookmarkSubscribeRSS Feed
arkam
Calcite | Level 5

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?

 

 

2 REPLIES 2
SuryaKiran
Meteorite | Level 14

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
SASKiwi
PROC Star

Try creating an aggregated measure summed by  - _BY_GROUP_.

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

Tips for filtering data sources in SAS Visual Analytics

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.

Discussion stats
  • 2 replies
  • 888 views
  • 0 likes
  • 3 in conversation