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!
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;
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;
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;
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 above solutions are working good!!
Thank you masters!
Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.