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;
Proc SQL;
Create table want as
select C_ID,store_nme , datetime
from have
group by 1,2
having datetime = max(datetime);
run;
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;
Worked very well.
I just prefer some sql atm. Thanks for the help! 😀
Proc SQL;
Create table want as
select C_ID,store_nme , datetime
from have
group by 1,2
having datetime = max(datetime);
run;
Thank you so much. 😃
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!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.