DATA Step, Macro, Functions and more

SAS Date Tracking

Accepted Solution Solved
Reply
New Contributor
Posts: 4
Accepted Solution

SAS Date Tracking

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


Accepted Solutions
Solution
‎05-19-2018 11:38 PM
Super User
Posts: 10,698

Re: SAS Date Tracking

Posted in reply to Daniel_Tan

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 .

View solution in original post


All Replies
Super User
Posts: 10,698

Re: SAS Date Tracking

Posted in reply to Daniel_Tan

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;

 

 

 

New Contributor
Posts: 4

Re: SAS Date Tracking

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.

Super User
Posts: 10,698

Re: SAS Date Tracking

Posted in reply to Daniel_Tan

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;

 

Solution
‎05-19-2018 11:38 PM
Super User
Posts: 10,698

Re: SAS Date Tracking

Posted in reply to Daniel_Tan

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 .

New Contributor
Posts: 4

Re: SAS Date Tracking

Thank you very much.
Super User
Posts: 10,698

Re: SAS Date Tracking

Posted in reply to Daniel_Tan

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;
New Contributor
Posts: 4

Re: SAS Date Tracking

Thank you very much for your help

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 7 replies
  • 172 views
  • 2 likes
  • 2 in conversation