find if the customer has another visit within 3 years

hk2013 — Wed, 10 Oct 2018 16:02:02 GMT

I have a list of customers and i need to check

Want

if customers labeled 'NEW' in a department where seen within 3 year of their visit this year (2018)

Have

Store Dept CustID Date Visit
Macys Shoe 1245 17-Feb-13 NEW
Macys Shoe 1245 25-Oct-15 OLD
Macys Bags 1245 20-Jun-10 New
Macys Shoe 1245 4-Apr-18 NEW
Macys Bags 1245 2-Feb-11 OLD
Macys Makeup 1333 17-Aug-16 NEW
Macys Bags 1333 25-Sep-12 OLD
Macys Makeup 1333 21-Sep-18 New
Macys Shoe 1333 21-Sep-10 NEW
Macys Bags 1333 2-Feb-11 OLD
Nords Shoes 1444 17-Aug-09 NEW
Nords Shoes 1444 25-Sep-12 OLD
Nords Makeup 1444 21-Sep-18 New
Nords Bags 1555 21-Feb-10 OLD
Nords Shoe 1555 1-Oct-18 OLD

want

Store Dept CustID Date Visit Within 3 years
Macys Shoe 1245 4-Apr-18 NEW YES
Macys Makeup 1333 21-Sep-18 New YES
Nords Makeup 1444 21-Sep-18 New NO

a new column that tells me (yes or no) if a customer that is new this year was seen in the same dept within 3 years from there new visit this year

Re: find if the customer has another visit within 3 years

PaigeMiller — Wed, 10 Oct 2018 17:29:47 GMT

UNTESTED CODE

Assumes your variable date is an actual SAS date value and not a character string.

proc sort data=have;
    by store dept custid date;
run;
data want;
    set have;
    length newcolumn $ 3;
    prev_date=lag(date);
    prev_custid=lag(custid);
    prev_dept=lag(dept);
    prev_store=lag(store);
    if year(date)=2018 and upcase(visit)='NEW' and prev_custid=custid 
        and prev_dept=dept and prev_store=store then do;
         if intck('year',prev_date,date,'c')<3 then newcolumn='YES';
         else newcolumn='NO';
         output;
    end;
    drop prev:;
run;

topic Re: find if the customer has another visit within 3 years in New SAS User

find if the customer has another visit within 3 years

Re: find if the customer has another visit within 3 years