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
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 .
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;
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.
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;
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 .
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;
Thank you very much for your help
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.