Desktop productivity for business analysts and programmers

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

Accepted Solution Solved
Reply
Contributor
Posts: 48
Accepted Solution

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

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


Accepted Solutions
Solution
‎09-22-2016 09:42 PM
Respected Advisor
Posts: 3,837

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

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;

View solution in original post


All Replies
Solution
‎09-22-2016 09:42 PM
Respected Advisor
Posts: 3,837

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

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;
Contributor
Posts: 48

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

Thanks Patrick,

 

 

Right there in front of my eyes.

 

Works now.

 

Cheers

Grand Advisor
Posts: 10,223

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

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; 
☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 346 views
  • 0 likes
  • 3 in conversation