Dealing with close change

[hkim3677]

Hi, 

I want to change a certain value of prior period that is very close to one of the following periods. 

For example,

Data want;

input Firm Year Change Solved;

datalines;

1001 2000 0 0

1001 2001 0 0

1001 2002 1 0

1001 2003 0 0

1001 2004 1 1

1001 2005 0 0

1001 2006 0 0

1002 2002 0 0

1002 2003 1 0

1002 2004 1 0

1002 2005 1 1

1002 2006 0 0

1002 2007 0 0

1003 1999 0 0

1003 2000 1 1

1003 2001 0 0

1003 2002 0 0

1003 2003 0 0

1003 2004 1 1

1003 2005 0 0

;

run;

The solved column is what I want to have in my dataset.

If a firm has a change = 1 in very close period (within 3 years) with the next period when change = 1, I want to make the former change = 1 to 0. That is, I want to have at least 3 years gap between changes by firms.

Please help me this problem.

Thank you!

[SuryaKiran]

I'm not sure if I understood your requirement or not. Also wondering how did you get the highlighted result, since the years are not >=3 years. I made a solution depending on my understanding on your requirement.

---

@hkim3677 wrote:

Hi, 

I want to change a certain value of prior period that is very close to one of the following periods. 

For example,

Data want;

input Firm Year Change Solved;

datalines;

1001 2000 0 0

1001 2001 0 0

1001 2002 1 0

1001 2003 0 0

1001 2004 1 1

1001 2005 0 0

1001 2006 0 0

1002 2002 0 0

1002 2003 1 0

1002 2004 1 0

1002 2005 1 1

1002 2006 0 0

1002 2007 0 0

1003 1999 0 0

1003 2000 1 1

1003 2001 0 0

1003 2002 0 0

1003 2003 0 0

1003 2004 1 1

1003 2005 0 0

;

run;

The solved column is what I want to have in my dataset.

If a firm has a change = 1 in very close period (within 3 years) with the next period when change = 1, I want to make the former change = 1 to 0. That is, I want to have at least 3 years gap between changes by firms.

Please help me this problem.

Thank you!

---

 Data have;
input Firm Year Change Solved;
datalines;
1001 2000 0 0
1001 2001 0 0
1001 2002 1 0
1001 2003 0 0
1001 2004 1 1
1001 2005 0 0
1001 2006 0 0
1002 2002 0 0
1002 2003 1 0
1002 2004 1 0
1002 2005 1 1
1002 2006 0 0
1002 2007 0 0
1003 1999 0 0
1003 2000 1 1
1003 2001 0 0
1003 2002 0 0
1003 2003 0 0
1003 2004 1 1
1003 2005 0 0
;
run;
proc sort data=have;
by firm year;
run;
DATA want(Drop=dif count);
retain count;
set have ;
by firm year;
dif=dif(year);
if first.firm then do;
			dif=0;
			count=dif;
			end;
else count=count+dif;
if change=1 and count<3 then new_solve=0;
else if change=1 and count>=3 then do;
						new_solve=1;
						count=dif;
						end;
else new_solve=change;
run;
 

[novinosrin]

Data have;

input Firm Year Change ;

datalines;
1001 2000 0 0
1001 2001 0 0
1001 2002 1 0
1001 2003 0 0
1001 2004 1 1
1001 2005 0 0
1001 2006 0 0
1002 2002 0 0
1002 2003 1 0
1002 2004 1 0
1002 2005 1 1
1002 2006 0 0
1002 2007 0 0
1003 1999 0 0
1003 2000 1 1
1003 2001 0 0
1003 2002 0 0
1003 2003 0 0
1003 2004 1 1
1003 2005 0 0
;

run;

data want;
do _n_=1 by 1 until(last.firm);
set have;
by firm;
array h(1000) _temporary_;/*arbitrary*/
if first.firm then call missing(of h(*));
if  _t and change and year-_t<=3 then h(_n_)=_t;
if Change then _t=year;
end;
do until(last.firm);
set have;
by firm;
if change and year not in h then solved=1;
else solved=0;
output;
drop  _t;
end;
run;

[Patrick · Accepted solution]

And here another solution option:

Data source;
  input Firm Year Change Solved;
  datalines;
1001 2000 0 0
1001 2001 0 0
1001 2002 1 0
1001 2003 0 0
1001 2004 1 1
1001 2005 0 0
1001 2006 0 0
1002 2002 0 0
1002 2003 1 0
1002 2004 1 0
1002 2005 1 1
1002 2006 0 0
1002 2007 0 0
1003 1999 0 0
1003 2000 1 1
1003 2001 0 0
1003 2002 0 0
1003 2003 0 0
1003 2004 1 1
1003 2005 0 0
;
run;

data target(drop=_:);

  if _n_=1 then
    do;
      dcl hash h1(dataset:'source (where=(change=1))');
      h1.defineKey('firm','year');
      h1.defineDone();
    end;

  set source;

  solved2=change;
  if change=1 then
    do _year=year+1 to year+3;
      if h1.check(key:firm, key:_year)=0 then
        do;
          solved2=0;
          leave;
        end;
    end;
run;

[hkim3677]

All above solutions are working good!!

Thank you masters!