BookmarkSubscribeRSS Feed
Khang
Obsidian | Level 7

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
3 REPLIES 3
Reeza
Super User

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

Shmuel
Garnet | Level 18

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;
mkeintz
Jade | Level 19

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

--------------------------

SAS INNOVATE 2024

Innovate_SAS_Blue.png

Registration is open! SAS is returning to Vegas for an AI and analytics experience like no other! Whether you're an executive, manager, end user or SAS partner, SAS Innovate is designed for everyone on your team. Register for just $495 by 12/31/2023.

If you are interested in speaking, there is still time to submit a session idea. More details are posted on the website. 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Get the $99 certification deal.jpg

 

 

Back in the Classroom!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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