How do I select one row based on max value with more than one grouping variable/column?

[Pili1100]

I want to get the maximum datetime per customer-id (C_ID) and per store. How do I do it?

By.first and/or SQL max + grouping?

Many thanks 😄

/*Ex.*/
DATA WORK.have;
FORMAT datetime datetime.;
INFILE DATALINES DELIMITER =','; 
INPUT C_ID $ store_nme $ datetime datetime.  ;
CARDS; 
AB,Store 1,17JAN2022:17:45:44
AB,Store 1,15JAN2021:17:55:40
AB,Store 1,25JAN2021:20:00:00
AB,Store 2,31OCT2020:12:00:01
AB,Store 2,05FEB2019:17:46:44
AB,Store 3,01AUG2021:16:02:16
AB,Store 3,01AUG2021:16:00:25
CD,Store 1,07MAR2019:03:12:11
CD,Store 1,11DEC2020:05:07:08
CD,Store 2,28DEC2020:12:00:01
CD,Store 2,28DEC2020:12:01:11
;
RUN;


PROC SORT DATA=have;
BY C_ID store_nme descending datetime ;
RUN;
DATA want;
SET have;
row_want = _N_;
IF row_WANT IN (1,4,6,8,10);
RUN; 

[Reeza]

PROC MEANS?

proc means data=have MAX NWAY;
class C_ID store_nme;
var datetime;
output out=want max(datetime) = max_datetime;
run;

proc print data=want;
format max_datetime datetime.;
run;

---

@Pili1100 wrote:

I want to get the maximum datetime per customer-id (C_ID) and per store. How do I do it?

By.first and/or SQL max + grouping?

Many thanks 😄

/*Ex.*/
DATA WORK.have;
FORMAT datetime datetime.;
INFILE DATALINES DELIMITER =','; 
INPUT C_ID $ store_nme $ datetime datetime.  ;
CARDS; 
AB,Store 1,17JAN2022:17:45:44
AB,Store 1,15JAN2021:17:55:40
AB,Store 1,25JAN2021:20:00:00
AB,Store 2,31OCT2020:12:00:01
AB,Store 2,05FEB2019:17:46:44
AB,Store 3,01AUG2021:16:02:16
AB,Store 3,01AUG2021:16:00:25
CD,Store 1,07MAR2019:03:12:11
CD,Store 1,11DEC2020:05:07:08
CD,Store 2,28DEC2020:12:00:01
CD,Store 2,28DEC2020:12:01:11
;
RUN;


PROC SORT DATA=have;
BY C_ID store_nme descending datetime ;
RUN;
DATA want;
SET have;
row_want = _N_;
IF row_WANT IN (1,4,6,8,10);
RUN; 

---

[Pili1100]

Worked very well. 

I just prefer some sql atm. Thanks for the help! 😀

[r_behata · Accepted solution]

Proc SQL;
Create table want as
select C_ID,store_nme , datetime
from have
group by 1,2
having datetime = max(datetime);
run;

[Pili1100]

Thank you so much. 😃