code to create an indicator variable

[shalmali]

Hi All,

I have a dataset and want to created a new variable, POST, based on the information I have. The data includes information on ticker, year, and Restate. I want to create a new variable, POST, which will take value 1 for period post restatement (period after Restate =1). That is, if my sample data is as follows:

DATA HAVE;

  INPUT  TICKER $ year RESTATE ;

CARDS;

A  2000    0

A  2001     0

A  2002     0

A  2003     0

A  2004     1

A  2005     0

A  2006      0

B  2000    0

B  2001    0

B  2002    1

B  2003    0

B  2004    0

B  2005    0

B  2006     0

;

RUN;

For firm A, restatement occurred in 2004. I want POST to be 0 for period prior to 2004 and 1 for period 2004 onwards.  Similarly for B, POST will be 0 for period prior to 2002 and 1 for period post restatement. That is, the output should be:

ticker year     restate     POST

A  2000           0               0

A  2001          0               0

A  2002          0               0

A  2003          0               0

A  2004          1               1

A  2005          0               1

A  2006          0               1

B  2000          0               0

B  2001          0               0

B  2002          1               1

B  2003          0               1

B  2004          0               1

B  2005          0               1

B  2006          0               1

If a company has two restatement in the sample period, then I want POST to be 2 after the second restatement. For instance if the data is as follows:

DATA HAVE;

  INPUT  name $ year RESTATE ;

CARDS;

A  2000    0

A  2001     1

A  2002     0

A  2003     0

A  2004     1

A  2005     0

A  2006      0

;

RUN;

The output should be

ticker year     restate     POST

A  1999           0               0

A  2000

0

0

A  2001          1               1

A  2002          0               1

A  2003          0               1

A  2004          1               2

A  2005          0               2

A  2006          0               2

I would appreciate if someone could share a code with me.

Thank you for your time.

[PGStats · Accepted solution]

Simple, POST is the cumulative sum of RESTATE within TICKER :

data want;

set have; by ticker;

if first.ticker then post = 0;

post + restate;

run;

PG

[shalmali]

Thanks PG Stat for the prompt reply.