Disclaimer  Chatgpt helped med writh much of this but the text says what I want it to,  Description: Currently, the aggregation.aggregate action in CAS requires separate runs to produce multiple grouping levels (e.g., detail rows plus totals by a subset of the groupBy variables). Unlike other SAS aggregation tools (such as PROC MEANS with the WAYS statement), there is no built-in way to produce hierarchical roll-ups or all-combinations totals in a single execution. Proposed Enhancement: Introduce an parameter in aggregation.aggregate bygroup parameter similar to ALL in proc tabulate or the capabilities like PROC MEANS  WAYS statement. Alternativly, parameters such as this below could be an alternative. Use case: Performance: Potentially reduces the need for multiple passes over large CAS tables to generate totals at different levels, which saves CPU, memory, and I/O. Convenience: Aligns the CAS Aggregation.aggregate action with PROC MEANS / PROC SUMMARY capabilities, reducing the learning curve for SAS users transitioning to CAS actions. Scalability: Particularly beneficial for large public-sector or healthcare datasets where multiple aggregation levels are needed for reporting and policy analysis, and where processing time is critical. To my knowledge, the CAS Aggregation.aggregate action is the only way to realtivly easeley calculate rolling window statistics (such as rolling 12 months) of the NDISTICT statistica. Combining this with the possibility to easily calculate group totals of statistics  would be very useful. Example Use Case: Aa analytics team needs: Detailed monthly counts by department, sub-benefit, gender, decision outcome. Totals by gender across all other variables. Totals by department only. Grand total. Today, these require multiple aggregation.aggregate calls and post-processing merges. With some sort of by group total parameter, all could be produced in a single action step. Currently I have been able to simulate this usinf Cas arrays and formats, but the numbers of  needed dimensions in the array grow exponentially with the number of variable you need to have totals for, eventuall becoming unweildly. ... View more

Hi all, This is my first post (I think?) asking for help in the forum.    I have a situation where I need to calculate a monthly year-to-date (YTD) distinct count of the number of customers benefiting from a particular insurance. As an example, for January 2023, the number should simply be the number of distinct customers that benefited during that month. For February 2023, it should be the number of distinct customers that benefited from the isurance at least once durin the period 2023-01-01 to 2023-02-28 and so on, all the way to December. This needs to be done for three years and is a repeated process. Currently, the organization I am working for uses a PROC SQL Macro loop to achieve this and to calculate rolling 12-month values of the same statistic. However, this process is very slow, sometimes taking upwards of 2 hours with the data we have. When not working with values that need to be distinct, I have gradually transitioned to using a multilevel format and PROC SUMMARY to achieve this instead. (Up until recently, PROC EXPAND has not been an option.) However, when distinct counts are required, this method does not work. In order to try and solve this problem, I decided to explore PROC CAS and the aggregation.aggregate action, as it is capable of doing distinct calculations. Luckily, I think I have figured out how to do the rolling twelve-month calculations that way, but I do not yet grasp the procedure well enough to do YTD. Does anyone have any ideas on how PROC CAS could be leveraged to achieve this? I will provde the code i used to calculate R12 as soon as I can access it.   SAMPLE DATA (abridged): Variable names and datat structure are acureate, Content is nonsense. A lagers saple dataset is attaced as a csv. DATA WORK.TESTDATA_CAS; LENGTH ID $ 3 DELFORMAN $ 12 AVDELNING $ 6 KON 8 DATE 8 ; LABEL AVDELNING = "AVDELNING" DATE = "DATUM" ; FORMAT ID $CHAR3. DELFORMAN $CHAR12. AVDELNING $CHAR6. KON BEST12. DATE DATE9. ; INFORMAT ID $CHAR3. DELFORMAN $CHAR12. AVDELNING $CHAR6. KON BEST12. DATE DATE9. ; INFILE DATALINES4 DLM='7F'x MISSOVER DSD ; INPUT ID : $CHAR3. DELFORMAN : $CHAR12. AVDELNING : $CHAR6. KON : BEST32. DATE : BEST32. ; DATALINES4; F68MintSweet222763 F51Cookie DoughSour222840 F97StrawberryBitter221974 F23Cookie DoughBitter123335 F73Cookie DoughSour222171 F73ChocolateSweet122491 F0StrawberrySalty122153 F47StrawberrySweet223739 F38StrawberrySweet121952 F83Cookie DoughSalty221939 F31MintSour122324 F90StrawberryBitter222375 F38PistachioSour223509 F30Cookie DoughSour122902 F72ChocolateSweet223355 F80MintBitter222271 F65PistachioBitter222297 F61PistachioSour223096 F83ChocolateSalty122483 F60MintSour123371 F66Cookie DoughBitter122770 F90PistachioBitter222793 F82Cookie DoughSour123460 F19PistachioBitter222837 F20ChocolateSalty121926 F27ChocolateBitter223201 F3VanillaSalty223595 F57Cookie DoughSalty222363 F7StrawberrySour223130 F87Cookie DoughSweet222676 F78StrawberrySweet123052 F79MintSour121940 F45Cookie DoughBitter223721 F10MintSour123437 F48ChocolateSalty122422 F98ChocolateSweet123483 F72VanillaSour121976 F49Cookie DoughBitter122143 F99MintSour122521 F26StrawberrySweet122973 F83ChocolateSour223643 F44ChocolateSour123394 F96StrawberrySweet123526 F96MintSalty122805 F56ChocolateSour123204 F35ChocolateBitter222243 F36StrawberrySalty223101 F64VanillaBitter122352 F7MintSweet223281 F53VanillaSweet123305 F97VanillaSour223403 F72VanillaBitter123688 F30ChocolateSalty123137 F8Cookie DoughSour123438 F56ChocolateBitter121930 F49ChocolateSour223234 F53PistachioSalty122782 F67PistachioSour222425 F53VanillaSour123726 F26ChocolateSour222898 F45VanillaBitter123177 F45PistachioSalty222495 F38Cookie DoughSweet223239 F30MintBitter121920 F33VanillaSweet123158 F23Cookie DoughSweet122933 F7MintBitter223704 F8StrawberryBitter223391 F46VanillaSalty223371 F0VanillaSalty222721 F62StrawberrySour123324 F64StrawberrySweet122533 F6VanillaSalty123233 F8StrawberrySour123056 F75ChocolateSalty222319 F41MintBitter223618 F16MintSalty122911 F68MintSweet223701 F37ChocolateSalty122508 F56StrawberrySweet222480 F59Cookie DoughBitter222666 F91StrawberrySweet221926 F33VanillaSalty223147 F78PistachioSour222079 F25MintBitter222792 F53StrawberrySalty123232 F97Cookie DoughBitter223457 F13VanillaBitter121992 F15ChocolateSalty123372 F27ChocolateSalty123515 F5Cookie DoughSour122528 F87VanillaBitter122783 F94MintBitter122726 F63StrawberrySalty223737 F82VanillaSweet223621 F34ChocolateSalty223015 F32Cookie DoughBitter122986 F87Cookie DoughSalty122681 F62StrawberrySour121996 F3Cookie DoughSweet223659 F89Cookie DoughSalty222123 F93VanillaSour223383 F25MintBitter123534 F25StrawberrySalty122436 F76VanillaBitter122967 F25MintSour223242 F0VanillaBitter122908 F38PistachioSour122379 F37VanillaSour123440 ;;;; RUN; Thank you in advance.            ... View more