Obsidian | Level 7

## calculating lag 1, 2 and 3

I want to calculate 3 lag values of invest i.e. lag1, lag2, lag3, but the problem is some of the years in my data are missing, how could it be treated

 year CUSIP Invest 2003 BN5522 3109000 2004 BN5522 2997000 2005 BN5522 3496000 2006 BN5522 2998873 2009 BN5522 4642000 2010 BN5522 5006000 2011 BN5522 4837000 2012 BN5522 6312000 2003 APC6748L 26679 2004 APC6748L 29661 2005 APC6748L 65606 2007 APC6748L 28439 2008 APC6748L 18375 2011 APC6748L 62001 2012 APC6748L 39696 2013 APC6748L 39492 2003 BM01045 . 2004 BM01045 0 2005 BM01045 741997 2008 BM01045 80947 2009 BM01045 142666
3 REPLIES 3
Super User

## Re: calculating lag 1, 2 and 3

If you have SAS/ETS use PROC EXPAND. You may need to use PROC TIMESERIES to complete the series.

Garnet | Level 18

## Re: calculating lag 1, 2 and 3

Instead of using LAG it will be easier to transpose the dataset and then

define array of yearly invests. Try:

``````proc transpose data=have
out=temp(drop=_name_) profix=yr;
by CUSIP;
id YEAR;
var INVEST;
run;data temp1; set temp;     array y_invest yr2000-yr2015;     ...  any other code to do the job ...run;``````

## Re: calculating lag 1, 2 and 3

I take it that you only want exact 1-year, 2-year, and 3-year lags.  This program does that.

Note I use nested IFN's.   That's because every alternative outcome in an IFN is evaluated, whether the if-condition is true or not (i.e. regardless of which outcome is chosen).  This ensures that the fifo queues underlying the LAG functions are always properly synchronized.

data want (drop=seq);

set have;

by cusip;

seq+1;

if first.cusip then seq=1;

lgyears1=   ifn(seq>1,ifn(lag1(year)=year-1,lag1(invest),.),.);

lgyears2=   ifn(seq>2,ifn(lag2(year)=year-2,lag2(invest),.),.);

lgyears3=   ifn(seq>3,ifn(lag3(year)=year-3,lag3(invest),.),.);

run;

Now, if you only want to keep years which have a complete set of non-missing lags, then put a subsetting IF statement

if nmiss(lgyears1,lgyears2,lgyears3)=0;

just before the RUN statement.

--------------------------
The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set

Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for
Allow PROC SORT to output multiple datasets

--------------------------
Discussion stats
• 3 replies
• 994 views
• 4 likes
• 4 in conversation