topic Re: SAS Date Tracking in SAS Programming

SAS Date Tracking

Daniel_Tan — Fri, 18 May 2018 12:43:03 GMT

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

Re: SAS Date Tracking

Ksharp — Fri, 18 May 2018 15:07:05 GMT

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;

Re: SAS Date Tracking

Daniel_Tan — Sat, 19 May 2018 02:53:08 GMT

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.

Re: SAS Date Tracking

Ksharp — Sat, 19 May 2018 12:36:07 GMT

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;

Re: SAS Date Tracking

Ksharp — Sat, 19 May 2018 12:39:06 GMT

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 .

Re: SAS Date Tracking

Daniel_Tan — Sun, 20 May 2018 03:39:39 GMT

Thank you very much.

Re: SAS Date Tracking

Ksharp — Sun, 20 May 2018 11:56:04 GMT

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;

Re: SAS Date Tracking

Daniel_Tan — Tue, 22 May 2018 15:23:40 GMT

Thank you very much for your help