topic Re: Add subtotal for select rows in SAS Enterprise Guide

Add subtotal for select rows

jen123 — Fri, 27 Jun 2014 21:12:46 GMT

Hello,

Here is an example of my data structure:

ProjectName	ExpenseType	AccountType	Amount
A	Revenue	Revenue	1,200,000
A	One-Time Expense	Operations	10,000
A	One-Time Expense	Technnology	10,000
A	Ongoing Expense	Operations - Production	100,000
A	Ongoing Expense	Operations - NonProduction	50,000
A	Ongoing Expense	Operations - ProjectManagement	25,000
A	Ongoing Expense	Technology	500,000
A	Ongoing Expense	Depreciation	15,000
A	Ongoing Expense	Maintenance	75,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.

ProjectName	ExpenseType	AccountType	Amount
A	Revenue	Revenue	1,200,000
A	One-Time Expense	Operations	10,000
A	One Time Expense	Technology	10,000
A	Ongoing Expense	Operations	175,000
A	Ongoing Expense	Technology	500,000
A	Ongoing Expense	Depreciation	15,000
A	Outgoing Expense	Maintenance	75,000
A	Total	Total	1,985,000

All advice are greatly appreciated!

Thanks!

Re: Add subtotal for select rows

Reeza — Fri, 27 Jun 2014 21:31:08 GMT

Did you want to use point and click or code?

Re: Add subtotal for select rows

jen123 — Sat, 28 Jun 2014 00:21:22 GMT

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!

Re: Add subtotal for select rows

Reeza — Sat, 28 Jun 2014 18:58:30 GMT

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;

Re: Add subtotal for select rows

jen123 — Sat, 28 Jun 2014 19:24:13 GMT

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!