Hi SAS experts, I have a list of consumer id, date of first purchase, date of subsequent purchase (if any) and with all these, I want to find out when do people repurchase. Do people repurchase after X number of months? Below is my code, proc sql; create table FUTURE_PURCHASES as SELECT consumer_id, case when sub_purchase_date between intnx('day',first_purchase_date,1,'e') and intnx('day',first_purchase_date,30,'e') then 1 else 0 end as month_1, case when sub_purchase_date between intnx('day',first_purchase_date,1,'e') and intnx('day',first_purchase_date,61,'e') then 1 else 0 end as month_2, case when sub_purchase_date between intnx('day',first_purchase_date,1,'e') and intnx('day',first_purchase_date,91,'e') then 1 else 0 end as month_3, case when sub_purchase_date between intnx('day',first_purchase_date,1,'e') and intnx('day',first_purchase_date,122,'e') then 1 else 0 end as month_4, case when sub_purchase_date between intnx('day',first_purchase_date,1,'e') and intnx('day',first_purchase_date,152,'e') then 1 else 0 end as month_5, case when sub_purchase_date between intnx('day',first_purchase_date,1,'e') and intnx('day',first_purchase_date,183,'e') then 1 else 0 end as month_6, case when sub_purchase_date between intnx('day',first_purchase_date,1,'e') and intnx('day',first_purchase_date,213,'e') then 1 else 0 end as month_7, case when sub_purchase_date between intnx('day',first_purchase_date,1,'e') and intnx('day',first_purchase_date,244,'e') then 1 else 0 end as month_8, case when sub_purchase_date between intnx('day',first_purchase_date,1,'e') and intnx('day',first_purchase_date,274,'e') then 1 else 0 end as month_9, case when sub_purchase_date between intnx('day',first_purchase_date,1,'e') and intnx('day',first_purchase_date,305,'e') then 1 else 0 end as month_10, case when sub_purchase_date between intnx('day',first_purchase_date,1,'e') and intnx('day',first_purchase_date,335,'e') then 1 else 0 end as month_11, case when sub_purchase_date between intnx('day',first_purchase_date,1,'e') and intnx('day',first_purchase_date,366,'e') then 1 else 0 end as month_12 from TOTAL_SALES order by consumer_id; quit; I know this may the the worst code you have ever seen but I am trying my best to make it shorter. Is there a way to shorten this code? Thank you!
... View more