Desktop productivity for business analysts and programmers

Add subtotal for select rows

Accepted Solution Solved
Reply
Frequent Contributor
Posts: 94
Accepted Solution

Add subtotal for select rows

Hello,

Here is an example of my data structure:

ProjectNameExpenseTypeAccountTypeAmount
ARevenueRevenue1,200,000
AOne-Time ExpenseOperations10,000
AOne-Time ExpenseTechnnology10,000
AOngoing ExpenseOperations - Production100,000
AOngoing ExpenseOperations - NonProduction50,000
AOngoing ExpenseOperations - ProjectManagement25,000
AOngoing ExpenseTechnology500,000
AOngoing ExpenseDepreciation15,000
AOngoing ExpenseMaintenance75,000

I would like to use the query builder in SAS EG to generate this resultant table.  The Ongoing Expense for Operations are summed together. Not sure if this is possible.

ProjectNameExpenseTypeAccountTypeAmount
ARevenueRevenue1,200,000
AOne-Time ExpenseOperations10,000
AOne Time ExpenseTechnology10,000
AOngoing ExpenseOperations175,000
AOngoing ExpenseTechnology500,000
AOngoing ExpenseDepreciation15,000
AOutgoing ExpenseMaintenance75,000
ATotalTotal1,985,000

All advice are greatly appreciated!

Thanks!


Accepted Solutions
Solution
‎06-28-2014 02:58 PM
Super User
Posts: 19,006

Re: Add subtotal for select rows

For the record it seems weird to add revenue and expenses to a total.

Try something like the following:

1. In Query Builder create a query that selects all columns and then create a computed column using the Advanced Calculation. The computed column will use the scan function to scan for a hyphen and take the first argument , scan(account_type, 1, "-") which will convert the Operations - XXXXX fields to Operations.

2. Use either a summary or summary table calculation to get the table required using the new computed column.

Or the code below may work (untested):

proc sql;

create table want as

select * from

(select ProjectName, ExpenseType, scan(AccountType, 1, "-") as AccountType, sum(Amount) as Amount

from have

group by ProjectName, ExpenseType, calculated AccountType)

union

(select ProjectName, 'Total' as ExpenseType,  'Total' as AccountType, sum(Amount) as Amount

from have)

) as A;

quit;

View solution in original post


All Replies
Super User
Posts: 19,006

Re: Add subtotal for select rows

Did you want to use point and click or code?

Frequent Contributor
Posts: 94

Re: Add subtotal for select rows

I would prefer point and click, but will take code.  I am a beginnner to coding but should be able to figure out the code.

Thanks Reeza!

Solution
‎06-28-2014 02:58 PM
Super User
Posts: 19,006

Re: Add subtotal for select rows

For the record it seems weird to add revenue and expenses to a total.

Try something like the following:

1. In Query Builder create a query that selects all columns and then create a computed column using the Advanced Calculation. The computed column will use the scan function to scan for a hyphen and take the first argument , scan(account_type, 1, "-") which will convert the Operations - XXXXX fields to Operations.

2. Use either a summary or summary table calculation to get the table required using the new computed column.

Or the code below may work (untested):

proc sql;

create table want as

select * from

(select ProjectName, ExpenseType, scan(AccountType, 1, "-") as AccountType, sum(Amount) as Amount

from have

group by ProjectName, ExpenseType, calculated AccountType)

union

(select ProjectName, 'Total' as ExpenseType,  'Total' as AccountType, sum(Amount) as Amount

from have)

) as A;

quit;

Frequent Contributor
Posts: 94

Re: Add subtotal for select rows

Hi Reeza - Thanks for the feedback and I will give it a try. 

FYI - I forgot to put the negative signs for expense.  Hence, that is why I am "adding" revenue and expenses.  With the negative, it's actually subtracting the expenses.

Thanks again!

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 1365 views
  • 0 likes
  • 2 in conversation