How to find if a person satisfies two separate conditions

[deedums]

Hi! I have a dataset which includes person IDs and one record for each of their transactions, looking something like this:

UIN	PURCHASEID	CREATIONDATE
ID_0045	60636	11-May-17
ID_0073	60646	13-Aug-17
ID_0073	63179	28-Dec-17
ID_0073	64001	07-Aug-18
ID_0084	61952	01-Aug-17
ID_0084	60539	01-Jun-18
ID_0092	60686	13-May-17
ID_0092	63302	28-May-18
ID_0092	60465	07-Aug-18

I want to create a code that flags when a person has made a purchase both in this past 12 months, and as well as having made a purchase during the previous 12 months, so that it may look something like this:

UIN	PURCHASEID	CREATIONDATE	FLAG
ID_0045	60636	11-May-17	0
ID_0073	60646	13-Aug-17	1
ID_0073	63179	28-Dec-17	1
ID_0073	64001	07-Aug-18	1
ID_0084	61952	01-Aug-17	0
ID_0084	60539	01-Jun-18	0
ID_0092	60686	13-May-17	1
ID_0092	63302	28-May-18	1
ID_0092	60465	07-Aug-18	1

I'm not sure how exactly to go about this. I tried to use a dataset grouped by UIN (the dataset is already sorted by UIN) but this did not work as each record only has one creationdate. And, this is working from a fixed point in time so would not work for my whole dataset anyway.

data purchases2;
set purchases1;
	by UIN;
		if (creationdate between '08aug2017'd and '07aug2018'd) 
		and (creationdate between '08aug2016'd and '07aug2017'd)
		then flag=1; else flag=0;
run;

This seems quite complex but I'm hoping there's a simple solution!

Thank you! 🙂

[data_null__]

I think you need a flag for each condition.

data purchase;
   input UIN $ PURCHASEID $ CREATIONDATE :date9.;
   format CR: date11.;
   cards;
ID_0045 60636 11-May-17 
ID_0073 60646 13-Aug-17 
ID_0073 63179 28-Dec-17 
ID_0073 64001 07-Aug-18 
ID_0084 61952 01-Aug-17 
ID_0084 60539 01-Jun-18 
ID_0092 60686 13-May-17 
ID_0092 63302 28-May-18 
ID_0092 60465 07-Aug-18 
   run;
proc print;
   run;
data who;
   do until(last.uin);
      set purchase;
      by uin;
      if '08aug2017'd le creationdate le '07aug2018'd then flag1=1;
      if '08aug2016'd le creationdate le '07aug2017'd then flag2=1;
		end;
   if flag1 and flag2;
   keep uin;
   run;
proc print;
   run;
      

[gamotte]

Hello,

Shouldn't the flag also be 1 for ID 84 ?

With proc sql :

proc sql noprint;
    CREATE TABLE want AS
    SELECT *,
           CASE WHEN sum(CREATIONDATE BETWEEN '08aug2017'd AND '07aug2018'd)
                 AND sum(CREATIONDATE BETWEEN '08aug2016'd AND '07aug2017'd)
           THEN 1 ELSE 0 END AS FLAG
    FROM have
    GROUP BY UIN    
    ;
quit;

Edit : Corrected typo  : 07aug2017 instead of 07aug2018 in the second interval.

[deedums]

Hi!

Thank you for your solution! 🙂

No ID 84 wouldn't be flagged as I would want the flag to show when the UIN has made a purchase in BOTH the past twelve months AND the previous twelve months. ID84 only made purchases in the past 12 months. 

[gamotte]

What about PURCHASEID 61952 ? 01-Aug-2017 is between 08-Aug-2016 and 07-Aug-2017 so shouldn't it be counted as a purchase in the previous twelve months ?

[deedums]

Sorry for the confusion, I'd meant the 12 months leading up to the final purchase per ID. 

[gamotte]

OK but why is ID 73 flagged ?

proc sql noprint;
    CREATE TABLE have2 AS
    SELECT *, max(CREATIONDATE) AS FINALPURCHASE format=date9.
    FROM have
    GROUP BY UIN;

    CREATE TABLE want AS
    SELECT *, CREATIONDATE BETWEEN intnx('year',FINALPURCHASE,-1,'s') AND FINALPURCHASE-1 AS LAST_12,
           CREATIONDATE BETWEEN intnx('year',FINALPURCHASE,-2,'s')AND intnx('year',FINALPURCHASE-1,-1,'s') AS PREV_12,
           CASE WHEN sum(CREATIONDATE BETWEEN intnx('year',FINALPURCHASE,-1,'s') AND FINALPURCHASE-1)
                 AND sum(CREATIONDATE BETWEEN intnx('year',FINALPURCHASE,-2,'s')AND intnx('year',FINALPURCHASE-1,-1,'s'))
           THEN 1 ELSE 0 END AS FLAG
    FROM have2
    GROUP BY UIN    
    ;
quit;