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;
... View more
