topic Top Observations Of Each Grouping in SAS Programming

Top Observations Of Each Grouping

mhoward2 — Mon, 07 Aug 2023 18:19:59 GMT

I tried a few solutions online and kept getting errors. How do output the top 10 observations by grouping? Current code:

PROC SQL;
	CREATE TABLE WORK.WANT AS
		SELECT  t1.BUCKET1,
				t1.BUCKET2,
				t1.CODE,
				SUM(CASE WHEN t1.CRITERIA1='A' THEN t1.SUM_OF_CNT
					END) AS A_CNT
		FROM WORK.HAVE t1
		WHERE t1.CRITERIA2 = 'B'
		GROUP BY 1,2,3
		ORDER BY t1.BUCKET1,
				 t1.BUCKET2,
				 CALCULATED A_CNT DESC;
QUIT;

There can be any number of combinations of BUCKET1 and BUCKET2. With this code, each combination gets as many rows as there are CODES within that combination. How do I only output the top 10 codes for each combination?

Thanks in advance!

Re: Top Observations Of Each Grouping

PaigeMiller — Mon, 07 Aug 2023 18:28:04 GMT

proc rank data=have descending;
    var a_cnt;
    by bucket1 buckt2;
    ranks a_cnt_ranks;
run;

When a_ctn_ranks is <= 10, those are the observations you want.

Re: Top Observations Of Each Grouping

mhoward2 — Mon, 07 Aug 2023 18:32:41 GMT

Thank you! Is there no way to do it within the same SQL Step?

Re: Top Observations Of Each Grouping

PaigeMiller — Mon, 07 Aug 2023 19:31:36 GMT

I would not even try to do this in SQL. It is the wrong tool for this problem. SAS has already programmed this for you in PROC RANK.

Can it be done in SQL? I'm sure it can, I'm sure it is more difficult than PROC RANK, and I don't know how to do this in SQL (nor do I want to know).

Re: Top Observations Of Each Grouping

ballardw — Mon, 07 Aug 2023 22:50:52 GMT

I really hate requests involving an adjective like "top" without defining it. What does top mean in this context? The COUNT of combinations with the most observations is only ONE possible interpretation.

Top could be by alphabetic or numeric value order the bucket variable(s)

By the observations with the largest count of Bucket1 for each value of Bucket2 or vice versa.

I'm sure I could come up with a few.

Do not expect use to use your code for definition because you have said it is getting errors. So it obviously does not contain a workable definition of "top".

BTW, any question about errors in code should include the LOG with the code and all the messages as well as the errors.

Example data can go a long way as well. What if your data does not have any observations where Criteria1='A' and Criteria2='B'?

Re: Top Observations Of Each Grouping

sbxkoenk — Mon, 07 Aug 2023 23:03:02 GMT

Hello,

in case you are using SAS Viya and CAS tables (datasets in CASLIBs) ...

A useful action is simple.topK, which selects the top K and bottom K values for variables in a data set, based on a user-specified ranking order. The example below returns the top 5 and bottom 5 values for two variables based on their frequency:

proc cas;
simple.topk / table="TABLE_NAME" 
              aggregator="N",                        
              inputs={"VAR1","VAR2"},
              topk=5,
              bottomk=5;
quit;

Simple is a rich action set with heaps of useful options covered in the documentation.

Koen