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)
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;
PROC FREQ does this job well -- check this example.
For more variations, here's another sample on the SAS support site.
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;
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;
@YNWA1 What would happen should there ever be a month missing in your data?
Can you be sure that there is always only one date per month?
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.
Then you should use my method with the calculated month difference.
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.
Find more tutorials on the SAS Users YouTube channel.