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!
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;
Did you want to use point and click or code?
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!
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;
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!
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.
Find more tutorials on the SAS Users YouTube channel.