Help using Base SAS procedures

calculating lag 1, 2 and 3

Reply
Contributor
Posts: 22

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

yearCUSIPInvest
2003BN55223109000
2004BN55222997000
2005BN55223496000
2006BN55222998873
2009BN55224642000
2010BN55225006000
2011BN55224837000
2012BN55226312000
2003APC6748L26679
2004APC6748L29661
2005APC6748L65606
2007APC6748L28439
2008APC6748L18375
2011APC6748L62001
2012APC6748L39696
2013APC6748L39492
2003BM01045.
2004BM010450
2005BM01045741997
2008BM0104580947
2009BM01045142666
Super User
Posts: 19,877

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. 

Trusted Advisor
Posts: 1,586

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;
Trusted Advisor
Posts: 1,022

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.

Ask a Question
Discussion stats
  • 3 replies
  • 262 views
  • 4 likes
  • 4 in conversation