Solved
Contributor
Posts: 29

# Retain only -3 and +3 period obs

Hi,

I need to retain only -3 and +3 period observations for each group.

Now my data looks like...

Data example;

input firm manager \$ year turnover post_turnover turnover_year;

datalines;

1001 abc 2005 0 0 .

1001 abc 2006 0 0 .

1001 abc 2007 1 1 2007

1001 abc 2008 0 0 .

1001 abc 2008 0 1 .

1001 abc 2009 0 0 .

1001 abc 2009 0 1 .

1001 abc 2010 0 0 .

1001 abc 2010 0 1 .

1001 abc 2011 0 0 .

1001 abc 2011 0 1 .

1001 abc 2012 1 1 2012

1001 abc 2013 0 1 .

1002 def 1990 0 0 .

1002 def 1991 0 0 .

1002 def 1992 0 0 .

1002 def 1993 0 0 .

1002 def 1994 1 1 1994

1002 def 1995 0 0 .

1002 def 1995 0 1 .

1002 def 1996 1 1 1996

1002 def 1996 0 1 .

;

run;

As you can see, I have multiple turnovers by each firm. So, observations between two turnovers were duplicated because they can be both post and pre.

Now my problem is how I can retain only observations within three years around turnover_year (i.e., -3 and +3) for each firm. Also, I need to exclude the observations where the turnover was occurred.

Please help me this problem.

Thank you SAS masters!

Accepted Solutions
Solution
‎03-13-2018 06:37 PM
Super User
Posts: 13,518

## Re: Retain only -3 and +3 period obs

You don't say if Manager plays any role in the selection process.

See if this gets close:

```proc sql;
create table want as
select distinct b.*
from (select * from example
where not missing(turnover_year) )as a
left join
example as b
on a.firm=b.firm
and abs (a.year - b.year) le 3
and missing(b.turnover_year)
;
quit;```

All Replies
Solution
‎03-13-2018 06:37 PM
Super User
Posts: 13,518

## Re: Retain only -3 and +3 period obs

You don't say if Manager plays any role in the selection process.

See if this gets close:

```proc sql;
create table want as
select distinct b.*
from (select * from example
where not missing(turnover_year) )as a
left join
example as b
on a.firm=b.firm
and abs (a.year - b.year) le 3
and missing(b.turnover_year)
;
quit;```
Contributor
Posts: 29

## Re: Retain only -3 and +3 period obs

Amazing Thank you!

Super User
Posts: 10,770

## Re: Retain only -3 and +3 period obs

``````Data example;
input firm manager \$ year turnover post_turnover turnover_year;
datalines;
1001 abc 2005 0 0 .
1001 abc 2006 0 0 .
1001 abc 2007 1 1 2007
1001 abc 2008 0 0 .
1001 abc 2008 0 1 .
1001 abc 2009 0 0 .
1001 abc 2009 0 1 .
1001 abc 2010 0 0 .
1001 abc 2010 0 1 .
1001 abc 2011 0 0 .
1001 abc 2011 0 1 .
1001 abc 2012 1 1 2012
1001 abc 2013 0 1 .
1002 def 1990 0 0 .
1002 def 1991 0 0 .
1002 def 1992 0 0 .
1002 def 1993 0 0 .
1002 def 1994 1 1 1994
1002 def 1995 0 0 .
1002 def 1995 0 1 .
1002 def 1996 1 1 1996
1002 def 1996 0 1 .
;
run;
data key;
set example(where=(turnover_year is not missing));
do i=turnover_year-3 to turnover_year+3;
year=i;output;
end;
keep firm manager year;
run;
data want;
if _n_=1 then do;
if 0 then set key;
declare hash h(dataset:'key');
h.definekey('firm', 'manager', 'year');
h.definedone();
end;
set example;
if h.check()=0;
run;

proc print;run;``````
☑ This topic is solved.

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

Discussion stats
• 3 replies
• 136 views
• 1 like
• 3 in conversation