SAS Date Tracking

Question

I have the following data set, which i wish to get the final maturity date for the roll over transaction( if (maturity date - subsequent issue date) fell between 0 and 1, then considered as roll over transaction). Please advise how to write the coding in SAS to obtain the final maturity date (Maturity Date_1) from maturity date.

Number  Amount  Issue_Date  Maturity_Date   Maturity date_1 Remarks
1       10000   29-Sep-16   28-Oct-16       24-Apr-17       Roll over
2       10000   28-Oct-16   28-Nov-16                       Roll over from 1
3       10000   28-Oct-16   28-Nov-16       28-Nov-16       Distinct Transaction
4       10000   28-Nov-16   29-Dec-16                       Roll over from 2
5       10000   29-Dec-16   27-Jan-17                       Roll over from 4
6       10000   26-Jan-17   27-Feb-17                       Roll over from 5
7       10000   27-Feb-17   24-Mar-17                       Roll over from 6
8       10000   24-Mar-17   24-Apr-17                       Roll over from 7

Thanks in advance

Answer 1

You can do this in a single data step; use the Retain functionality. In my solution below:

1. I retained the values of the previous record to calculate the 0 or 1,
2. At the end I saved the finale Maturity date in a macro variable then updated the first record only.

Solution:

Data preparation & formatting:

data have;
length number 8. Amount 8. Issue_Date $9. Maturity_Date $9.   ;
input Number  Amount  Issue_Date $ Maturity_Date $  ;

datalines;
1       10000   29-Sep-16   28-Oct-16   
2       10000   28-Oct-16   28-Nov-16    
3       10000   28-Oct-16   28-Nov-16       
4       10000   28-Nov-16   29-Dec-16     
5       10000   29-Dec-16   27-Jan-17    
6       10000   26-Jan-17   27-Feb-17      
7       10000   27-Feb-17   24-Mar-17     
8       10000   24-Mar-17   24-Apr-17     
;
run;

proc sql; 
create table have_dates as select
number, amount,input(Issue_Date,date9.)as Issue_Date format=date9.,  input(Maturity_Date,date9.) as Maturity_Date format=date9.,
. as Maturity_date_1 format=date9. ,"" as Remarks length=20 format=$20.
from have 
;
quit;

Logic:

data want;
set have_dates end=eof;
retain Issue_prv;
retain maturity_prv;
retain number_prv;
if _n_ = 1 then do; 
    Issue_prv=Issue_Date;
    maturity_prv=Maturity_Date;
    number_prv=number;
    output;
end;
if _n_ > 1 then do;
    if (issue_date = Issue_prv and Maturity_Date=maturity_prv) 
        then do; Remarks="Distinct Transaction"; Maturity_date_1=Maturity_Date; end;
    if (intck('Day',Issue_Date,maturity_prv) = 0 or intck('Day',Issue_Date,maturity_prv)= 1)
    then do; Remarks=catx(" ","Roll over from ",number_prv); number_prv=number;end;
    if (eof and Remarks ne "") then do ; final=Maturity_Date; call symput("final_maturity",Maturity_Date); end ;
    output; 
    Issue_prv=Issue_Date;
    maturity_prv=Maturity_Date;
    end;
    format Issue_prv maturity_prv final date9.;
    keep number amount Issue_Date Maturity_Date Maturity_date_1  Remarks;
run;
proc sql;
update want
    set Maturity_date_1= &final_maturity. , Remarks="Roll over"
    where Remarks="";
quit;

Output: