Calculate the Number of loans the company had over the past 5 years

[Ksharp]

If you don't have big table, SQL is good for you.
While data step is better choice .

[Ksharp]

OK. Here is .


data have;
infile cards truncover expandtabs;
input CompanyID	LoanID	FYEAR;
cards;
1004	5006	1989
1004	5006	1990
1004	6460	1990
1004	5006	1991
1004	6460	1991
1004	7970	1991
1004	5006	1992
1004	6460	1992
1004	7970	1992
1004	5006	1993
1004	7970	1993
1004	5006	1994
1004	7970	1994
1004	5006	1995
1004	7970	1995
1004	7970	1996
1004	34433	1996
1004	34441	1996
1004	7970	1997
1004	34433	1997
1004	34441	1997
1004	45856	1997
1004	46572	1997
1004	49136	1997
1004	7970	1998
1004	34433	1998
1004	34441	1998
1004	45856	1998
1004	46572	1998
1004	49136	1998
1004	34433	1999
1004	34441	1999
1004	46572	1999
1004	49136	1999
1004	34433	2000
1004	34441	2000
1004	100472	2000
1004	34433	2001
1004	34441	2001
1004	111500	2001
1004	34433	2002
1004	34441	2002
1004	109872	2002
1004	119544	2002
1004	34433	2003
1004	34441	2003
1004	109872	2003
1004	119544	2003
1004	109872	2004
1004	119544	2004
1004	153686	2006
1004	153686	2007
1004	165317	2007
1004	153686	2008
1004	165317	2008
1004	153686	2009
1004	165317	2009
1004	187804	2009
1004	165317	2010
1004	197270	2010
1004	187804	2010
1004	197270	2011
1004	187804	2011
1004	197270	2012
1004	187804	2012
1004	197270	2013
1004	187804	2013
1004	197270	2014
1004	241601	2014
;
run;

proc SQL;
create table temp as
select CompanyID,FYEAR,count(distinct LoanID) as N_LOANS_1YEAR
from have 
group by CompanyID,FYEAR;
quit;
data want;
 set temp;
 by CompanyID;
 array x{1800:2200} _temporary_;
 if first.CompanyID then do;n=0;call missing(of x{*});end;
 n+1;
 x{FYEAR}=N_LOANS_1YEAR;
 MAX_LOANS_5YEAR=max(x{FYEAR-4},x{FYEAR-3},x{FYEAR-2},x{FYEAR-1},x{FYEAR});
 if n ge 5 then 
 AVG_LOANS_5YEAR=mean(x{FYEAR-4},x{FYEAR-3},x{FYEAR-2},x{FYEAR-1},x{FYEAR});
 drop n;
run;