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. 😃
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: