topic Re: How do I select one row based on max value with more than one grouping variable/column? in SAS Programming

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

Pili1100 — Mon, 17 Jan 2022 19:57:10 GMT

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;

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

Reeza — Mon, 17 Jan 2022 20:02:22 GMT

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;

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

r_behata — Mon, 17 Jan 2022 20:44:44 GMT

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

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

Pili1100 — Tue, 18 Jan 2022 07:23:02 GMT

Thank you so much. 😃

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

Pili1100 — Tue, 18 Jan 2022 07:24:07 GMT

Worked very well.

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