topic Re: Top N per group using Datastep in SAS Enterprise Guide

Top N per group using Datastep

YNWA1 — Thu, 07 Oct 2021 11:09:46 GMT

Hi,

Can someone help me getting the 4 most recent month per group from the below example

The by condition i am using is lob descending business_date descending num_excpt;

data top4;
infile datalines dlm=",";
input business_date :date9. oic_name :$10. lob :$15. lead :$21. num_excpt;
format business_date date9.;
datalines;
31Jan2021,TOMI,DEP_A,JANE,10
31Jan2021,JAKE,DEP_A,JANE,8
31Jan2021,JENY,DEP_A,JANE,5
31Jan2021,PAUL,DEP_A,JANE,4
31Dec2020,KENY,DEP_A,JANE,3
31Dec2020,LORA,DEP_A,JANE,2
31Dec2020,RAFT,DEP_A,JANE,2
31Dec2020,JOEL,DEP_A,JANE,1
30NOV2020,YANE,DEP_A,JANE,5
30NOV2020,ALLE,DEP_A,JANE,4
30NOV2020,JOHN,DEP_A,JANE,2
30NOV2020,JOEY,DEP_A,JANE,1
31OCT2020,TEEL,DEP_A,JANE,12
31OCT2020,RAVI,DEP_A,JANE,10
31OCT2020,RUEL,DEP_A,JANE,6
31OCT2020,JANE,DEP_A,JANE,4
30SEP2020,PATTY,DEP_A,JANE,6
30SEP2020,JOLY,DEP_A,JANE,2
30SEP2020,BURT,DEP_A,JANE,1
30SEP2020,TERY,DEP_A,JANE,1
31AUG2020,JEEN,DEP_A,JANE,8
31AUG2020,LORA,DEP_A,JANE,7
31AUG2020,RAFT,DEP_A,JANE,5
31AUG2020,JOEL,DEP_A,JANE,2
31JUL2020,KENY,DEP_A,JANE,15
31JUL2020,LORA,DEP_A,JANE,10
31JUL2020,RAFT,DEP_A,JANE,8
31JUL2020,JOEL,DEP_A,JANE,6
30JUN2020,KENY,DEP_A,JANE,14
30JUN2020,LORA,DEP_A,JANE,10
30JUN2020,RAFT,DEP_A,JANE,8
30JUN2020,JOEL,DEP_A,JANE,5
31Dec2020,KENY,DEP_B,MARK,3
31Dec2020,LORA,DEP_B,MARK,2
31Dec2020,RAFT,DEP_B,MARK,2
31Dec2020,JOEL,DEP_B,MARK,1
30NOV2020,YANE,DEP_B,MARK,5
30NOV2020,ALLE,DEP_B,MARK,4
30NOV2020,JOHN,DEP_B,MARK,2
30NOV2020,JOEY,DEP_B,MARK,1
31OCT2020,TEEL,DEP_B,MARK,12
31OCT2020,RAVI,DEP_B,MARK,10
31OCT2020,RUEL,DEP_B,MARK,6
31OCT2020,JANE,DEP_B,MARK,4
30SEP2020,PATTY,DEP_B,MARK,6
30SEP2020,JOLY,DEP_B,MARK,2
30SEP2020,BURT,DEP_B,MARK,1
30SEP2020,TERY,DEP_B,MARK,1
31AUG2020,JEEN,DEP_B,MARK,8
31AUG2020,LORA,DEP_B,MARK,7
31AUG2020,RAFT,DEP_B,MARK,5
31AUG2020,JOEL,DEP_B,MARK,2
31JUL2020,KENY,DEP_B,MARK,15
31JUL2020,LORA,DEP_B,MARK,10
31JUL2020,RAFT,DEP_B,MARK,8
31JUL2020,JOEL,DEP_B,MARK,6
30JUN2020,KENY,DEP_B,MARK,14
30JUN2020,LORA,DEP_B,MARK,10
30JUN2020,RAFT,DEP_B,MARK,8
30JUN2020,JOEL,DEP_B,MARK,5
30SEP2019,PATTY,DEP_C,GAEL,6
30SEP2019,JOLY,DEP_C,GAEL,2
30SEP2019,BURT,DEP_C,GAEL,1
30SEP2019,TERY,DEP_C,GAEL,1
31AUG2019,JEEN,DEP_C,GAEL,8
31AUG2019,LORA,DEP_C,GAEL,7
31AUG2019,RAFT,DEP_C,GAEL,5
31AUG2019,JOEL,DEP_C,GAEL,2
31JUL2019,KENY,DEP_C,GAEL,15
31JUL2019,LORA,DEP_C,GAEL,10
31JUL2019,RAFT,DEP_C,GAEL,8
31JUL2019,JOEL,DEP_C,GAEL,6
30JUN2019,KENY,DEP_C,GAEL,14
30JUN2019,LORA,DEP_C,GAEL,10
30JUN2019,RAFT,DEP_C,GAEL,8
30JUN2019,JOEL,DEP_C,GAEL,5
31MAY2019,KENY,DEP_C,GAEL,3
31MAY2019,LORA,DEP_C,GAEL,2
31MAY2019,RAFT,DEP_C,GAEL,2
31MAY2019,JOEL,DEP_C,GAEL,1
30APR2019,YANE,DEP_C,GAEL,5
30NOV2019,ALLE,DEP_C,GAEL,4
30NOV2019,JOHN,DEP_C,GAEL,2
30NOV2020,JOEY,DEP_C,GAEL,1
;
run;

I want to output the below (4 most recent month for each lob)

Re: Top N per group using Datastep

ChrisHemedinger — Thu, 07 Oct 2021 11:24:56 GMT

PROC FREQ does this job well -- check this example.

For more variations, here's another sample on the SAS support site.

Re: Top N per group using Datastep

Kurt_Bremser — Thu, 07 Oct 2021 11:38:32 GMT

You already sorted correctly, so you only need a RETAINed variable to keep the most recent date:

proc sort data=top4;
by lob descending business_date descending num_excpt;
run;

data want;
set top4;
by lob;
retain dt;
if first.lob
then dt = business_date;
else if intck('month',business_date,dt) > 3 then delete;
run;

Re: Top N per group using Datastep

YNWA1 — Fri, 08 Oct 2021 02:36:06 GMT

Hi KurtBremser,

Thanks for the solution. It makes sense.

I have used the below code to generate same result. I would like to get your thought if this is less efficient compared to your solution.

proc sort data=top4;
by lob descending business_date descending num_excpt;
run;

data test;
set top4;
by lob descending business_date descending num_excpt;
if first.business_date then month+1;
if first.lob then month=1;
if month<=6 then output;
run;

Re: Top N per group using Datastep

Patrick — Fri, 08 Oct 2021 04:34:54 GMT

@YNWA1 What would happen should there ever be a month missing in your data?

Re: Top N per group using Datastep

Kurt_Bremser — Sat, 09 Oct 2021 08:14:00 GMT

Can you be sure that there is always only one date per month?

Re: Top N per group using Datastep

PaigeMiller — Sat, 09 Oct 2021 09:45:51 GMT

If you use PROC RANK, you have options on how to handle ties. If you use a DATA step or PROC FREQ, you have no such options.

Re: Top N per group using Datastep

YNWA1 — Sun, 10 Oct 2021 21:27:44 GMT

Hi,

My report is based on month end data and getting a count of exceptions for last 6 months per lob (line of business).

There can be cases where there are no exception at all for certain month.

Re: Top N per group using Datastep

Kurt_Bremser — Mon, 11 Oct 2021 06:30:07 GMT

Then you should use my method with the calculated month difference.