🔒 This topic is solved and locked.
Need further help from the community? Please
sign in and ask a new question.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Posted 12-08-2020 02:00 PM
(1347 views)
Hello, hope everyone is safe.
I have this data set and I want to flag a Customer_Key that have 3 consecutive datediff of less 1. What I'm trying to get is a customer that have a 3 or more transactions within a 2 day period. Thank you in advance.
1 ACCEPTED SOLUTION
Accepted Solutions
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Try this (made a small edit - see comment):
libname here '.';
proc import datafile="Data_Example.xlsx" out=have dbms=xlsx replace;
run;
proc sort;
by customer_key;
run;
data want;
retain window cnt;
set have;
by customer_key;
if first.customer_key then do;
cnt=0; window=0;
end;
if window + datediff <= 2 then do;
cnt=cnt+1; window = window + datediff;
end;
else do;
window=0; cnt = 1; /*changed cnt=0 to cnt=1*/
end;
if cnt >= 3 then flag='Y';
run;
2 REPLIES 2
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Try this (made a small edit - see comment):
libname here '.';
proc import datafile="Data_Example.xlsx" out=have dbms=xlsx replace;
run;
proc sort;
by customer_key;
run;
data want;
retain window cnt;
set have;
by customer_key;
if first.customer_key then do;
cnt=0; window=0;
end;
if window + datediff <= 2 then do;
cnt=cnt+1; window = window + datediff;
end;
else do;
window=0; cnt = 1; /*changed cnt=0 to cnt=1*/
end;
if cnt >= 3 then flag='Y';
run;
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Thank you, It worked as I expected.