Columns were not found in the contributing tables error. Calculated columns with Sums

[nicwoyak]

I have a table with reservation info and a table with vendor info that I am joining. I'm aggregating by vendor so that I can get the total count of reservations they did as well as the total amounts charged to the customer over their reservations and the total amounts charged by the vendor over their reservations. This all works fine. But when I add columns that calculate profit and gross profit percentage, I get an error "The following columns were not found in the contributing tables: CustomerTotal, VendorTotal." It seems to not like that I'm calculating with my sum columns in the same query. Is there a way to get around this or am I best off just doing 3 separate queries (one for the initial selects, one for calculating profit, one for calculating percent gross profit)?

proc sql;
create table work.VendorGP as
SELECT aa.AssociateName, COUNT(rr.ReservationID) AS ResCount, SUM(rr.CustomerBaseAmt) AS CustomerTotal, SUM(rr.VendorBaseAmt) AS VendorTotal,
CustomerTotal-VendorTotal as Profit, Profit/CustomerTotal as PercentGP format PERCENT6.4
FROM work.RecentResReservations rr JOIN active.ascAssociates aa
ON rr.AscVendorId = aa.AssociateID
GROUP BY aa.AssociateName;
quit;

I have a similar query that seems to run correctly but gives the note "The query requires remerging summary statistics back with the original data." What triggers that note - the count/sums, the group by clause, or the calculated columns?

[ballardw · Accepted solution]

When you reference something like:

CustomerTotal-VendorTotal as Profit

SQL expects that CustomerTotal and VendorTotal are variables in the source table. Since you are creating those variables as the same time then that is not the case (this time). So reuse the summary functions as used to create them:

SUM(rr.CustomerBaseAmt) - SUM(rr.VendorBaseAmt) AS Profit

Similar for the percent calculation. Though since you are using the calculated variable Profit it'll get a bit uglier.

In some cases you can use the CALCULATED instruction to mean use of the value you created:

Calculated(CustomerTotal) -Calculated(VendorTotal) as Profit.

You might want to be thankful that your source data set didn't have CustomerTotal or VendorTotal variables already existing as you'd likely go a bit bonkers trying to figure out whey the shown calculated Profit and PercentGP looked very odd or generated different warnings.

The question about remerging note would require the actual code to tell why.