Help to create a dataset

[shalmali]

Hello Everyone,

I need your help with the creation of a SAS code. I want to create a subsample from my full sample. My full sample has three variables: Firm_ID, YEAR, and DirCount. The full sample looks as follows:

Firm_ID     YEAR     Dir_Count

A               2000          0

A               2001          0

A               2002          0

A               2003          0

B               2000          1

B               2001          1

B               2002          3

B               2003          2

C               2000          0

C               2001          1

C              2002           1

C               2003          0

D              2000           0

D               2001          1

D               2002          1

D               2003          1

E               2000          2

E               2001          3

E               2002          0

E               2003          0

The variable DirCount is number of independent directors (zero means there are no independent directors. DirCount is not a dummy variable). I want to create a subsample that either changed permanently from having no independent directors to independent directors or from independent directors to no independent directors. In other words, I want the subsample to contain the following information from the above full sample:

Firm_ID     YEAR     Dir_Count

D              2000           0

D               2001          1

D               2002          1

D               2003          1

E               2000          2

E               2001          3

E               2002          0

E               2003          0

I would appreciate if someone provide me with the code to construct this dataset.

Thanks.

S

[Ksharp · Accepted solution]

If I understood what you mean.

Code: Program

data have;
input Firm_ID  $   YEAR   Dir_Count;
cards;
A 2000 0
A 2001 0
A 2002 0
A 2003 0 
B 2000 1
B 2001 1
B 2002 3
B 2003 2
C 2000 0
C 2001 1
C 2002 1
C 2003 0
D 2000 0
D 2001 1
D 2002 1
D 2003 1
E 2000 2
E 2001 3
E 2002 0
E 2003 0
;
run;
data want;
 do until(last.Firm_ID);
  set have;
  by Firm_ID ;
  if first.Firm_ID  then first=Dir_Count;
  if last.Firm_ID  then last=Dir_Count;
 end;
 do until(last.Firm_ID);
  set have;
  by Firm_ID ;
  if (first and not last) or (not first and last) then output;
 end;
 drop first last;
run;

Xia Keshan

[shalmali]

Thank you for the code.

S