BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
jen123
Fluorite | Level 6

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!

1 ACCEPTED SOLUTION

Accepted Solutions
Reeza
Super User

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

4 REPLIES 4
Reeza
Super User

Did you want to use point and click or code?

jen123
Fluorite | Level 6

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!

Reeza
Super User

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;

jen123
Fluorite | Level 6

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!

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

SAS Enterprise Guide vs. SAS Studio

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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