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
PROC Star

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.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

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

View all other training opportunities.

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