SAS EG Return MAX value based on Multiple criteria (SAS Version 7.11)

[DME790]

Hi All,

I am trying to get the MAX value returned based on two crieria (EVENT_DT and Cluster)

Data Example (data has multiple dates in the EVENT_DT

EVENT_DT     CLUSTER    MAX_ANSWER_TM
2016-09-21     AAAA          1306
2016-09-21     AAAA          1631
2016-09-21     AAAA          1918
2016-09-21    AAAA           2114
2016-09-21    BBBB           1235
2016-09-21    BBBB           1385
2016-09-21    BBBB           1326
2016-09-21    CCCC          2594
2016-09-21   CCCC           2626
2016-09-21   CCCC           2361
2016-09-21    CCCC          2408
2016-09-21    CCCC          2308
2016-09-21    DDDD         4196
2016-09-21    DDDD         3725
2016-09-21    DDDD        4519

Returning the following

EVENT_DT     CLUSTER     MAX_ANSWER_TM
2016-09-21      AAAA           2114
2016-09-21      BBBB          1385
2016-09-21      CCCC          2626
2016-09-21      DDDD         4519

I can get the following code to return the Max value for the first event_DT but need to get the Max value for the date and Cluster

Data Report.SSW_Max_Ans;
	Set Cartel.preagent_telephony;
	Keep EVENT_DT Cluster MAX_ANSWER_TM;
	Where EVENT_DT >='01Sep2016'd
		and CALL_TYPE = 'External'
		and Programme_nm = 'SSW'
		and QUEUE_TYPE = 'CHOPs';
	
Run;

Proc Sort data=Report.SSW_MAX_Ans;
	BY  Cluster EVENT_DT descending Max_answer_tm ;
Run;

data Work.SSW_MAX_Ans2;
	Set Report.SSW_MAX_Ans;
	By EVENT_DT ;
	if First.EVENT_DT;
Run;

EVENT_DT        MAX_ANSWER_TM
2016-09-01        7483
2016-09-02       8348
2016-09-03       1025
2016-09-04       706
2016-09-05       9179
2016-09-06       287
2016-09-07       7929
2016-09-08       8073
2016-09-09      7627

Any help is appreciated.

Cheers

Dean

[Patrick · Accepted solution]

Shouldn't your by group statement then include Cluster as well: "by Cluster Event_dt"?

data Work.SSW_MAX_Ans2;
	Set Report.SSW_MAX_Ans;
	By Cluster EVENT_DT ;
	if First.EVENT_DT;
Run;

[DME790]

Thanks Patrick,

Right there in front of my eyes.

Works now.

Cheers

[ballardw]

If your Max_answer_tm is a numeric value you could try this:

proc summary data=Set Cartel.preagent_telephony nway;
   Where EVENT_DT >='01Sep2016'd
   		and CALL_TYPE = 'External'
   		and Programme_nm = 'SSW'
   		and QUEUE_TYPE = 'CHOPs';

   class cluster event_dt;
   var Max_answer_tm;
   output out=Work.SSW_MAX_Ans2 (drop= _:) max=;
run;