topic Re: Creating a subsample from an existing sample in SAS Programming

Creating a subsample from an existing sample

AmirSari — Sat, 07 Oct 2017 21:48:24 GMT

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.

Re: Creating a subsample from an existing sample

error_prone — Sat, 07 Oct 2017 22:24:38 GMT

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

Re: Creating a subsample from an existing sample

PGStats — Sun, 08 Oct 2017 05:43:23 GMT

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;

Re: Creating a subsample from an existing sample

PGStats — Sun, 08 Oct 2017 05:48:36 GMT

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;

Re: Creating a subsample from an existing sample

AmirSari — Sun, 08 Oct 2017 06:06:21 GMT

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.

Re: Creating a subsample from an existing sample

Ksharp — Sun, 08 Oct 2017 07:38:46 GMT

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;

Re: Creating a subsample from an existing sample

AmirSari — Sun, 08 Oct 2017 14:09:36 GMT

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