Solved
Contributor
Posts: 29

# Dealing with close change

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.

Thank you!

Accepted Solutions
Solution
‎03-16-2018 07:52 PM
Posts: 4,741

## Re: Dealing with close change

[ Edited ]

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;``````

All Replies
Valued Guide
Posts: 597

## Re: Dealing with close change

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.

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;
``````
Thanks,
Suryakiran
PROC Star
Posts: 1,838

## Re: Dealing with close change

[ Edited ]
``````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;``````
Solution
‎03-16-2018 07:52 PM
Posts: 4,741

## Re: Dealing with close change

[ Edited ]

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;``````

Contributor
Posts: 29

## Re: Dealing with close change

All above solutions are working good!!

Thank you masters!

☑ This topic is solved.