How do I indicate based on a previous observation (t -1)?

[natebanger]

Hi,

I've got a data set like the following:

Name ID Year Compensation SP500

Ira 1001 2010 1466 0

Ira 1001 2011 1491 0

Ira 1001 2012 1634 1

Ira 1001 2013 1793 1

David 1002 2010 1289 0

David 1002 2011 1289 0 

David 1002 2012 1289 0 

David 1002 2013 1289 0 

William 1003 2010 3976 0

William 1003 2011 3986 0

William 1003 2012 3998 0

William 1003 2013 4205 1

John 1004 2010 2890 0

John 1004 2011 2890 1

John 1004 2012 2890 1

John 1004 2013 2890 1

Sarah 1005 2010 2476 0

Sarah 1005 2011 3125 1

Sarah 1005 2012 2992 0

Sarah 1005 2013 3261 1

Where the name is the name of the CEO of a company, the ID is the unique ID of that company, the Compensation is the CEO's annual salary for that year and the sp500 dummy variable indicates whether the company was in the S&P500 that year.

I'm attempting to create another dummy variable (SP500Entrant) indicating if the company entered the S&P500 that year. So the data set would look like this:

Name ID Year Compensation SP500 SP500Entrant

Ira 1001 2010 1466 0 0

Ira 1001 2011 1491 0 0

Ira 1001 2012 1634 1 1

Ira 1001 2013 1793 1 0

David 1002 2010 1289 0 0

David 1002 2011 1289 0 0

David 1002 2012 1289 0 0

David 1002 2013 1289 0 0

William 1003 2010 3976 0 0

William 1003 2011 3986 0 0

William 1003 2012 3998 0 0

William 1003 2013 4205 1 1

John 1004 2010 2890 0 0

John 1004 2011 3890 1 1

John 1004 2012 3911 1 0

John 1004 2013 4016 1 0

Sarah 1005 2010 2476 0 0

Sarah 1005 2011 3125 1 1

Sarah 1005 2012 2992 0 0

Sarah 1005 2013 3261 1 1

(ie. Ira's company (1001) enters the S&P500 in 2012 - indicated by SP500 equalling 0 for the previous year (2011) and 1 for 2012 - so I would want SP500Entrant to equal 1, though I wouldn't want SP500Entrant to equal 1 in 2013 because the company did not enter the S&P500 in 2013, they just remained in there- indicated by SP500 equalling 1 for the previous year (2012) and 1 for 2013).

Unfortunately I'm new to SAS and I'm unsure how this can be done. Basically, I want to indicate 1 only if the current dummy variable (SP500) observation equals 1 AND the previous dummy variable (SP500) observation equals 0. Does anyone know how I could write this code?

I've tried this:

But unfortunately the variable is uninitialised, and I'm also concerned it wouldn't identify if a company leaves the S&P500 and then returns. (eg. Sarah's company (1005) enters the S&P500 twice - once in 2011 and then again in 2013 (after leaving the index in 2012) - so this should be indicated in the SP500Entrant column twice (in 2011 and in 2013)).

Any help would be appreciated, thanks.

[RichardDeVen · Accepted solution]

You will want to track the prior SP500 value so that you can identify the entry criteria.  RETAIN the tracking variable so that it is not reset at the top of the step. Explicitly reset the tracking value to zero at the first row in the group.  This will allow the first row to be an 'entry' condition.  Update the tracking value after checking the criteria.

data want(drop=prior_SP500);
  set have;
  by id;

  retain prior_SP500;
  if first.id then prior_SP500 = 0;

  SP500Entrant = (prior_SP500 = 0 and sp500 = 1);
  prior_SP500 = sp500;
run;