Hi Yohann, I used a brute force method so there is a little repetition. With more values of ATC it might become worth it to look into macros, but this should give you the table you are after. Note I assumed three periods of 60 days, but you can add more where noted below. * prepare sample data; data med1; input id $ ref_date YYMMDD10. ATC reimb_date YYMMDD10.; datalines; 1ABC 2011-01-01 1 2011-02-01 1ABC 2011-01-01 1 2011-04-05 1ABC 2011-01-01 1 2011-06-17 1ABC 2011-01-01 2 2011-02-01 1ABC 2011-01-01 2 2011-04-05 1ABC 2011-01-01 2 2011-06-17 2KHG 2011-04-03 5 2011-04-05 2KHG 2011-04-03 5 2011-05-03 2KHG 2011-04-03 5 2011-06-05 ; run; * sort the data to ensure reimbursements are tested correctly in next step; proc sort data = med1; by id ATC reimb_date; run; * check if reimbursement occurred in 60 day periods; data med2; set med1; by ATC; retain reimb_period; if first.ATC then reimb_period = 1; else reimb_period + 1; reimb_datdif = datdif(ref_date,reimb_date,'ACT/ACT'); reimb_test = reimb_period * 60; if reimb_datdif le reimb_test then success = 1; else if reimb_datdif gt reimb_test then success = 0; run; * reshape table; data med3; set med2; by id; * I assume three reimbursement periods, but changing the _3 to another number will add more; retain ATC1_1-ATC1_3; retain ATC2_1-ATC2_3; retain ATC3_1-ATC3_3; retain ATC4_1-ATC4_3; retain ATC5_1-ATC5_3; array ATC1_array ATC1_1-ATC1_3; array ATC2_array ATC2_1-ATC2_3; array ATC3_array ATC3_1-ATC3_3; array ATC4_array ATC4_1-ATC4_3; array ATC5_array ATC5_1-ATC5_3; if first.id then do; call missing(of ATC1_array{*}); call missing(of ATC2_array{*}); call missing(of ATC3_array{*}); call missing(of ATC4_array{*}); call missing(of ATC5_array{*}); end; if ATC = 1 then ATC1_array[reimb_period] = success; else if ATC = 2 then ATC2_array[reimb_period] = success; else if ATC = 3 then ATC3_array[reimb_period] = success; else if ATC = 4 then ATC4_array[reimb_period] = success; else if ATC = 5 then ATC5_array[reimb_period] = success; * try commenting out this last conditional if you have never used this before, it works together with the RETAIN statement above to reshape the table one row at a time; if last.id; drop ATC reimb_date reimb_period reimb_datdif reimb_test success; run;
... View more