data input; input id stage date date9.; format date date9d.; cards; 8923767 1 30-Apr-19 8923767 1 31-May-19 8923767 2 30-Jun-19 8923767 2 31-Jul-19 8923767 2 31-Aug-19 8923767 2 30-Sep-19 8923767 2 31-Oct-19 8923768 1 30-Apr-19 8923768 1 31-May-19 8923768 1 30-Jun-19 8923768 1 31-Jul-19 8923768 1 31-Aug-19 8923768 1 30-Sep-19 8923768 1 31-Oct-19 8923769 2 30-Apr-19 8923769 2 31-May-19 8923769 3 30-Jun-19 8923769 4 31-Jul-19 8923769 2 31-Aug-19 8923769 2 30-Sep-19 8923769 2 31-Oct-19 ; run; data temp(drop = p_id p_stage); set input; by id; p_id = lag(id); p_stage = lag(stage); if p_id=id then do; if p_stage<stage then output; end; run; proc sql; create table temp1 as select id,max(stage) as stage ,max(date) as date format date9d. from temp group by id; proc transpose data=input out=input1(drop = name); by id; var stage; id date; run; proc sql; create table input2 as select a.*,b.date as month format=MONNAME3. from input1 as a left join temp1 as b on a.id=b.id; quit; I have used above code which worked fine for my requirements till Rate column. Now my requirement is for OVER COLUMN which is calculated as if there is any Month name in MONTH column then RATE of that month - last month RATE OVER = RATE - (last month)RATE (here last month is 31-Oct-2019 Please see the attachment
... View more