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.
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;
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;
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;
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.