Creating a subsample from an existing sample

[AmirSari]

Hi everyone,

I have a problem that I was hoping I could get some help with. I have a dataset that consists of more than 24000 firm-year observations and 74 variables. I am trying to select a subsample based on a dummy variable. Below is an excerpt from the data. I want to select firm-years for which the dummy variable for the current year is 1 and for AT LEAST the past and future three years are zeros. For example, in the subsample below I need the firm_id 29 for years 1997 through 2004, because the dummy variable for the year 2000 is one and for the past three years (i.e. 1997, 1998, and 1999) and for At Least the next three years (i.e. 2001, 2002, 2003, and 2004) is also zero. I also want firm_id 34 for years 1995 to 2003 because it has at least three zeros before and after 1999. I do not need the firm_id 29 for years 1995 and 1996 because there is not enough information (i.e. at least three years) before 1996.

Firm_id       year        dummy variable

29                1995                 0     

29                 1996                1   

29                 1997                0

29                 1998                0

29                 1999                0

29                 2000                1

29                 2001                0

29                 2002                0

29                 2003                0

29                 2004                0

34                 1995                0

34                 1996                0

34                 1997                0

34                 1998                0

34                 1999                1

34                 2000                0

34                 2001                0

34                 2002                0

34                 2003                0

Thanks in advance for your help.

[error_prone]

Search for "look back and ahead", http://sascommunity.org/wiki/Look-Ahead_and_Look-Back

[PGStats]

A SQL solution:

data have;
input id       year        dummy;
datalines;
29                 1995                0     
29                 1996                1   
29                 1997                0
29                 1998                0
29                 1999                0
29                 2000                1
29                 2001                0
29                 2002                0
29                 2003                0
29                 2004                0
34                 1995                0
34                 1996                0
34                 1997                0
34                 1998                0
34                 1999                1
34                 2000                0
34                 2001                0
34                 2002                0
34                 2003                0
;

proc sql;
create table want as
select a.id, a.year
from 
    have as a inner join
    have as b on a.id=b.id and abs(a.year-b.year) <= 3
where a.dummy=1
group by a.id, a.year
having sum(b.dummy=0) = 6;
select * from want;
quit;

[PGStats]

Or maybe what you want is:

proc sql;
create table want as
select b.id, b.year
from 
    have as a inner join
    have as b on a.id=b.id and abs(a.year-b.year) <= 3
where a.dummy=1
group by a.id, a.year
having sum(b.dummy=0) = 6
order by id, year;
select * from want;
quit;

[AmirSari]

Thank you so much for your reply. This is actually what I want except that year 2004 for firm_id 29 and years 1995 and 2003 are not included in the output.

[Ksharp · Accepted solution]

data have;
input id       year        dummy;
datalines;
29                 1995                0     
29                 1996                1   
29                 1997                0
29                 1998                0
29                 1999                0
29                 2000                1
29                 2001                0
29                 2002                0
29                 2003                0
29                 2004                0
34                 1995                0
34                 1996                0
34                 1997                0
34                 1998                0
34                 1999                1
34                 2000                0
34                 2001                0
34                 2002                0
34                 2003                0
;

data temp;
 set have;
 by id dummy notsorted;
 group+first.dummy;
run;
proc sql;
create table count as
 select id,group,dummy,count(*) as n
  from temp
   group by id,group,dummy;
quit;
data key;
 merge count count(firstobs=2 keep=id n rename=(id=_id n=_n));
 lag_n=lag(n);
 if id ne lag(id) then call missing(lag_n);

 next_n=_n;
 if id ne _id then call missing(next_n);

 if dummy=1 and lag_n>2 and next_n>2 then do;
   new_group=group-1;output;
   new_group=group;output;
   new_group=group+1;output;
 end;
 keep new_group;
run;

proc sql;
create table want as
 select *
  from temp
   where group in (select new_group from key);
quit;

[AmirSari]

Thank you so much, Ksharp! This gives me exactly what I want!