SAS Date Tracking

[Daniel_Tan]

I have the following data set, which i wish to get the final maturity date (maturity_Date_1) for the roll over transaction(condition-  if (maturity date - subsequent issue date) fell between 0 and 1, then considered as roll over transaction).I have been struggling for sometimes, but still not able to come up with the codes. Is it possible to use lag function and Do looping ?

Please advise how to write the coding in SAS to obtain the final maturity date (Maturity Date_1) from maturity date for all customers. Remarks is not as port of the output.

Cust_NO number  Amount  Issue_Date  Maturity_Date   Maturity_date_1 Remarks

B           1   10000   08-Sep-14   08-Dec-14                             08-Jun-15   Roll over

B           2   10000   08-Sep-14   08-Dec-14                             08-Jun-15   Roll Over

B           3   10000   08-Dec-14   09-Mar-15                                            .   Roll over from 1

B           4   10000   08-Dec-14   09-Mar-15                                            .   Roll over from 2

B           5   10000   09-Mar-15   08-Jun-15                                            .   Roll over from 3

B           6   10000   09-Mar-15   08-Jun-15                                            .   Roll over from 4

A           1   20000   29-Sep-16   28-Oct-16                             24-Apr-17   Roll over

A           2   20000   28-Oct-16   28-Nov-16                                            .   Roll over from 1

A           3   20000   28-Oct-16   28-Nov-16                            28-Nov-16   Distinct Transaction

A           4   20000   28-Nov-16   29-Dec-16                                          .   Roll over from 2

A           5   20000   29-Dec-16   27-Jan-17                                          .   Roll over from 4

A           6   20000   26-Jan-17   27-Feb-17                                          .   Roll over from 5

A           7   20000   27-Feb-17   24-Mar-17                                         .   Roll over from 6

A           8   20000   24-Mar-17   24-Apr-17                                          .   Roll over from 7

Thanks

[Ksharp]

That is really uneasy task , you need consider many different scenarios .

Assuming there are at most two obs in the same Cust_NO and  Issue_Date.

Good Luck.

data have;
input (Cust_NO number  Amount ) (: $20.) (Issue_Date  Maturity_Date) (: date9.)  ;
format Issue_Date  Maturity_Date date9.;
cards;
B           1   10000   08-Sep-14   08-Dec-14                             08-Jun-15   Roll over
B           2   10000   08-Sep-14   08-Dec-14                             08-Jun-15   Roll Over
B           3   10000   08-Dec-14   09-Mar-15                                            .   Roll over from 1
B           4   10000   08-Dec-14   09-Mar-15                                            .   Roll over from 2
B           5   10000   09-Mar-15   08-Jun-15                                            .   Roll over from 3
B           6   10000   09-Mar-15   08-Jun-15                                            .   Roll over from 4
A           1   20000   29-Sep-16   28-Oct-16                             24-Apr-17   Roll over
A           2   20000   28-Oct-16   28-Nov-16                                            .   Roll over from 1
A           3   20000   28-Oct-16   28-Nov-16                            28-Nov-16   Distinct Transaction
A           4   20000   28-Nov-16   29-Dec-16                                          .   Roll over from 2
A           5   20000   29-Dec-16   27-Jan-17                                          .   Roll over from 4
A           6   20000   26-Jan-17   27-Feb-17                                          .   Roll over from 5
A           7   20000   27-Feb-17   24-Mar-17                                         .   Roll over from 6
A           8   20000   24-Mar-17   24-Apr-17   
;
run;
data temp;
 set have;
 by Cust_NO Issue_Date  Maturity_Date notsorted;
 if first.Maturity_Date then group=0;
 group+1;
run;
data first second;
 set temp;
 if group=1 then output first;
  else output second;
 run;
 
 data _first;
  if _n_=1 then do;
   if 0 then set first(rename=(Maturity_Date=_Maturity_Date));
   declare hash h(dataset:'first(rename=(Maturity_Date=_Maturity_Date))');
   h.definekey('Cust_NO','Issue_Date');
   h.definedata('_Maturity_Date');
   h.definedone();  
  end;
 set first;
 call missing(_Maturity_Date);
 k=Maturity_Date;rc=h.find(key:Cust_NO,key:k);
 if rc ne 0 then do;k=Maturity_Date-1; rc=h.find(key:Cust_NO,key:k);end;
 do while(rc=0);
   h.remove(key:Cust_NO,key:k);
   k=_Maturity_Date;
   rc=h.find(key:Cust_NO,key:k);
   if rc ne 0 then do;k=_Maturity_Date-1; rc=h.find(key:Cust_NO,key:k);end;
 end;
 drop group k rc;
 run;
 
 
  data _second;
  if _n_=1 then do;
   if 0 then set second(rename=(Maturity_Date=_Maturity_Date));
   declare hash h(dataset:'second(rename=(Maturity_Date=_Maturity_Date))');
   h.definekey('Cust_NO','Issue_Date');
   h.definedata('_Maturity_Date');
   h.definedone();
  end;
 set second;
 call missing(_Maturity_Date);
 k=Maturity_Date;rc=h.find(key:Cust_NO,key:k);
 if rc ne 0 then do;k=Maturity_Date-1; rc=h.find(key:Cust_NO,key:k);end;
 do while(rc=0);
   h.remove(key:Cust_NO,key:k);
   k=_Maturity_Date;
   rc=h.find(key:Cust_NO,key:k);
   if rc ne 0 then do;k=_Maturity_Date-1; rc=h.find(key:Cust_NO,key:k);end;
 end;
 drop group k rc;
 run;
 proc sort data=_first;by Cust_NO number;run;
 proc sort data=_second;by Cust_NO number;run;
 data want;
  set _first(in=ina) _second(in=inb);
  by Cust_NO number;
  a=ina;b=inb;
 run;
 
 proc print noobs;run;

[Daniel_Tan]

Hi Ksharp,

For Cust_No A with Number 3, the expected maturity date should be 28-Nov-2016 (instead of missing) as this is a distinct transaction.

Need your advice on the followings:-

1). Is it possible to us lag function and do looping to perform the task ?

example :-

Uniqkey=cats(Cus_No,Amount);

Data Want;

Set Have;

by uniqkey;

if first.unikey then do;
issue_date1=Issue_date;
Differ=0;

Do i=2 to _N_;
do j=i-1  to 1;
     if (lagj(differ)=0 or lagj(differ)=1)and (issue_Date-lagj(maturity_Date) = 1 or issue_Date-lagj(maturity_Date)= 0) then do;
        issue_date1=lagj(Issue_date);
        Differ=issue_Date-lagj(maturity_Date);
        return;        
  end;
end;

end;

Run;

2). Presume the roll over transactions is 3, then i have to create 3 data sets to perform the task?

Thanks for your help.

[Ksharp]

Your question is very complicated. I would not expect my code work for all your situations.

You are on your own .

data have;
input (Cust_NO number  Amount ) (: $20.) (Issue_Date  Maturity_Date) (: date9.)  ;
format Issue_Date  Maturity_Date date9.;
cards;
B           1   10000   08-Sep-14   08-Dec-14                             08-Jun-15   Roll over
B           2   10000   08-Sep-14   08-Dec-14                             08-Jun-15   Roll Over
B           3   10000   08-Dec-14   09-Mar-15                                            .   Roll over from 1
B           4   10000   08-Dec-14   09-Mar-15                                            .   Roll over from 2
B           5   10000   09-Mar-15   08-Jun-15                                            .   Roll over from 3
B           6   10000   09-Mar-15   08-Jun-15                                            .   Roll over from 4
A           1   20000   29-Sep-16   28-Oct-16                             24-Apr-17   Roll over
A           2   20000   28-Oct-16   28-Nov-16                                            .   Roll over from 1
A           3   20000   28-Oct-16   28-Nov-16                            28-Nov-16   Distinct Transaction
A           4   20000   28-Nov-16   29-Dec-16                                          .   Roll over from 2
A           5   20000   29-Dec-16   27-Jan-17                                          .   Roll over from 4
A           6   20000   26-Jan-17   27-Feb-17                                          .   Roll over from 5
A           7   20000   27-Feb-17   24-Mar-17                                         .   Roll over from 6
A           8   20000   24-Mar-17   24-Apr-17   
;
run;
data temp;
 set have;
 by Cust_NO Issue_Date  Maturity_Date notsorted;
 if first.Maturity_Date then group=0;
 group+1;
run;
data first others;
 set temp;
 if group=1 then output first;
  else output others;
 run;
 
 data _first;
  if _n_=1 then do;
   if 0 then set first(rename=(Maturity_Date=_Maturity_Date));
   declare hash h(dataset:'first(rename=(Maturity_Date=_Maturity_Date))');
   h.definekey('Cust_NO','Issue_Date');
   h.definedata('_Maturity_Date');
   h.definedone();  
  end;
 set first;
 call missing(_Maturity_Date);
 k=Maturity_Date;rc=h.find(key:Cust_NO,key:k);
 if rc ne 0 then do;k=Maturity_Date-1; rc=h.find(key:Cust_NO,key:k);end;
 do while(rc=0);
   h.remove(key:Cust_NO,key:k);
   k=_Maturity_Date;
   rc=h.find(key:Cust_NO,key:k);
   if rc ne 0 then do;k=_Maturity_Date-1; rc=h.find(key:Cust_NO,key:k);end;
 end;
 drop group k rc;
 run;
 proc sort data=have;by Cust_NO Issue_Date Maturity_Date;run;
 proc sort data=_first;by Cust_NO Issue_Date Maturity_Date;run;
 data x;
  inb=0;
  merge have _first(keep=Cust_NO Issue_Date Maturity_Date _Maturity_Date in=inb);
  by Cust_NO Issue_Date Maturity_Date;
  flag=inb;
 run;
 proc sql;
 create table x1 as
  select *,sum(flag=0) as yes
   from x
    group by Cust_NO
      order by Cust_NO,Issue_Date,Maturity_Date;
quit;
data want;
 set x1;
 if yes=1 and flag=0 then _Maturity_Date=Maturity_Date;
 drop flag yes;
run;

[Ksharp · Accepted solution]

1) Sorry. I have no time to check your code ,and have no idea about it.

2)I changed my code, should cover two more obs in the same group .

[Daniel_Tan]

Thank you very much.

[Ksharp]

Hi,

Here is the fixed code, Maybe you should try it .

data have;
input (Cust_NO number  Amount ) (: $20.) (Issue_Date  Maturity_Date) (: date9.)  ;
format Issue_Date  Maturity_Date date9.;
cards;
B           1   10000   08-Sep-14   08-Dec-14                             08-Jun-15   Roll over
B           2   10000   08-Sep-14   08-Dec-14                             08-Jun-15   Roll Over
B           3   10000   08-Dec-14   09-Mar-15                                            .   Roll over from 1
B           4   10000   08-Dec-14   09-Mar-15                                            .   Roll over from 2
B           5   10000   09-Mar-15   08-Jun-15                                            .   Roll over from 3
B           6   10000   09-Mar-15   08-Jun-15                                            .   Roll over from 4
A           1   20000   29-Sep-16   28-Oct-16                             24-Apr-17   Roll over
A           2   20000   28-Oct-16   28-Nov-16                                            .   Roll over from 1
A           3   20000   28-Oct-16   28-Nov-16                            28-Nov-16   Distinct Transaction
A           4   20000   28-Nov-16   29-Dec-16                                          .   Roll over from 2
A           5   20000   29-Dec-16   27-Jan-17                                          .   Roll over from 4
A           6   20000   26-Jan-17   27-Feb-17                                          .   Roll over from 5
A           7   20000   27-Feb-17   24-Mar-17                                         .   Roll over from 6
A           8   20000   24-Mar-17   24-Apr-17   
;
run;

 data temp;
  if _n_=1 then do;
   if 0 then set have(rename=(Maturity_Date=_Maturity_Date));
   declare hash h(dataset:'have(rename=(Maturity_Date=_Maturity_Date))',multidata:'y');
   h.definekey('Cust_NO','Issue_Date');
   h.definedata('Cust_NO','_Maturity_Date');
   h.definedone();  
  end;
 set have end=last;
 call missing(_Maturity_Date);
 k=Maturity_Date;rc=h.find(key:Cust_NO,key:k);
 if rc=0 then h.removedup(key:Cust_NO,key:k);

 if rc ne 0 then do;
   k=Maturity_Date-1; 
   rc=h.find(key:Cust_NO,key:k);
   if rc=0 then h.removedup(key:Cust_NO,key:k);
 end;
 do while(rc=0);
   if h.check()=0 then h.removedup();
   k=_Maturity_Date;
   rc=h.find(key:Cust_NO,key:k);
   if rc=0 then h.removedup(key:Cust_NO,key:k);
   if rc ne 0 then do;
     k=_Maturity_Date-1; 
     rc=h.find(key:Cust_NO,key:k);
     if rc=0 then h.removedup(key:Cust_NO,key:k);
   end;
 end;
 if last then h.output(dataset:'rest');
 drop  k rc ;
 run;
 proc sort data=temp;by Cust_NO Maturity_Date;run;
 proc sort data=rest;by Cust_NO _Maturity_Date;run;
 data want;
  inb=0;
  merge temp rest(in=inb rename=(_Maturity_Date=Maturity_Date));
  by Cust_NO Maturity_Date;
  if inb then _Maturity_Date=Maturity_Date;
 run;

[Daniel_Tan]

Thank you very much for your help