Hello everyone, I've done something similar in the past, but now can't really remember how I did that. Hope someone can help me with this. Here's a sample of historical data and final result i'm aiming for Sample customer_id reporting_date rating_18_20 (flag) 111111 Apr-19 1 111111 May-19 1 111111 Jun-19 0 111111 Jul-19 0 111111 Aug-19 0 111111 Sep-19 0 111111 Oct-19 1 111111 Nov-19 1 111111 Dec-19 1 111111 Jan-20 0 222222 Apr-19 0 222222 May-19 0 222222 Jun-19 0 222222 Jul-19 0 222222 Aug-19 1 222222 Sep-19 1 222222 Oct-19 1 222222 Nov-19 1 222222 Dec-19 0 222222 Jan-20 0 333333 Apr-19 0 333333 May-19 0 333333 Jun-19 0 333333 Jul-19 0 333333 Aug-19 0 333333 Sep-19 1 333333 Oct-19 1 333333 Nov-19 1 333333 Dec-19 1 333333 Jan-20 1 444444 Apr-19 0 444444 May-19 0 444444 Jun-19 0 444444 Jul-19 0 444444 Aug-19 0 444444 Sep-19 0 444444 Oct-19 0 444444 Nov-19 0 444444 Dec-19 0 444444 Jan-20 0 555555 Apr-19 1 555555 May-19 1 555555 Jun-19 1 555555 Jul-19 1 555555 Aug-19 1 555555 Sep-19 1 555555 Oct-19 1 555555 Nov-19 1 555555 Dec-19 1 555555 Jan-20 1 Needed table: Data aggregated on customer level where reporting_date_rating is a date when customer got rating_18_20 =1 for the last time in history and period_rating_18_20 is amount of months the customer has been possessing the flag for the last period in history. customer_id reporting_date_rating period_rating_18_20 111111 Oct-19 3 222222 Aug-19 4 333333 Sep-19 5 444444 0 555555 Apr-19 10 Here's a code that doesn't work for customers who didn't have rating_18_20 =1 (for example customer number 444444) and returns period_rating_18_20 = 10 instead of 0. Could anyone please look into it and tell me where the logic is incorrect. Thanks. /*18-20*/ proc sort data=WORK.SAMPLE (keep=customer_id reporting_date rating_18_20) out=rating_flags_sort nodup; by customer_id rating_18_20 ; run; data rating; set rating_flags_sort; format for_lag_dt lag_dt date9.; by customer_id rating_18_20; retain num_of_period for_lag_dt cnt_months; if first.rating_18_20 then do; num_of_period = 1; for_lag_dt = reporting_date; cnt_months= 1; end; else do; lag_dt = for_lag_dt; for_lag_dt = reporting_date; a=intck('month', lag_dt, reporting_date); if a>1 then do; num_of_period+1; cnt_months =1;end; else cnt_months + 1; end; /*drop lag_dt for_lag_dt;*/ run; /*doesn't work for customers with 0 flags - returns max amount of months in history*/ data rating_2; set rating; by customer_id; if last.customer_id; keep customer_id rating_18_20 cnt_months; run; /*date of rating*/ proc sort data=rating out=rating_sort_2; by customer_id descending rating_18_20 descending num_of_period reporting_date; run; data rating_3; set rating_sort_2; by customer_id descending rating_18_20 descending num_of_period reporting_date; if first.customer_id and first.num_of_period; keep customer_id reporting_date; run; data rating_total; merge rating_2 rating_3; by customer_id; run;
... View more
