If you don't have big table, SQL is good for you. While data step is better choice .
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;
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.