DATA Step, Macro, Functions and more

Creating a subsample from an existing sample

Accepted Solution Solved
Reply
Contributor
Posts: 24
Accepted Solution

Creating a subsample from an existing sample

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.


Accepted Solutions
Solution
‎10-08-2017 10:09 AM
Super User
Posts: 10,044

Re: Creating a subsample from an existing sample

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;

View solution in original post


All Replies
Frequent Contributor
Posts: 149

Re: Creating a subsample from an existing sample

Search for "look back and ahead", http://sascommunity.org/wiki/Look-Ahead_and_Look-Back
Respected Advisor
Posts: 4,931

Re: Creating a subsample from an existing sample

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;
PG
Respected Advisor
Posts: 4,931

Re: Creating a subsample from an existing sample

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;
PG
Contributor
Posts: 24

Re: Creating a subsample from an existing sample

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.
Solution
‎10-08-2017 10:09 AM
Super User
Posts: 10,044

Re: Creating a subsample from an existing sample

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;
Contributor
Posts: 24

Re: Creating a subsample from an existing sample

Thank you so much, Ksharp! This gives me exactly what I want!
☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 6 replies
  • 135 views
  • 4 likes
  • 4 in conversation