topic Re: ERROR: Summary functions nested in this way are not supported. in SAS Programming

ERROR: Summary functions nested in this way are not supported.

carihuppert — Mon, 10 Jun 2024 18:27:45 GMT

proc sql;

create table MostPopularRouterByState as

select c.State, o.SKU, count(o.OrderID) as TotalOrders

from DATA_ELE.ORDERS o

inner join DATA_ELE.CUSTOMERS c on o.CustomerID = c.CustomerID

group by c.State, o.SKU

having calculated TotalOrders = (select max(calculated TotalOrders) from DATA_ELE.ORDERS o2

inner join DATA_ELE.CUSTOMERS c2 on o2.CustomerID = c2.CustomerID

77 where c.State = c2.State

78 group by c2.State, o2.SKU)

79 order by c.State, TotalOrders desc;

ERROR: Summary functions nested in this way are not supported.

mkeintz — Tue, 11 Jun 2024 01:07:35 GMT

Do you have a question?

LinusH — Tue, 11 Jun 2024 09:26:21 GMT

Your sub-query doesn't know about columns created in the outer query, so you need to replicate the logic from the outer query in your sub-query.

I don't know your requirement, but I would guess you wnat to have a GROUP BY in your outer query, or what is your desired en result?

Kurt_Bremser — Wed, 12 Jun 2024 09:52:38 GMT

Do you want the most sold sku for every state, or just the overall highest order count and the state/sku combination associated with it?