BookmarkSubscribeRSS Feed
carihuppert
Calcite | Level 5

 

 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.
3 REPLIES 3
mkeintz
PROC Star

Do you have a question?

--------------------------
The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set

Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for
Allow PROC SORT to output multiple datasets

--------------------------
LinusH
Tourmaline | Level 20

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?

Data never sleeps

hackathon24-white-horiz.png

The 2025 SAS Hackathon has begun!

It's finally time to hack! Remember to visit the SAS Hacker's Hub regularly for news and updates.

Latest Updates

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 3 replies
  • 955 views
  • 0 likes
  • 4 in conversation