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!
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.