BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
hkim3677
Calcite | Level 5

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!

1 ACCEPTED SOLUTION

Accepted Solutions
Patrick
Opal | Level 21

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;

 

 

View solution in original post

4 REPLIES 4
SuryaKiran
Meteorite | Level 14

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;
 
Thanks,
Suryakiran
novinosrin
Tourmaline | Level 20
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
Opal | Level 21

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
Calcite | Level 5

All above solutions are working good!!

 

Thank you masters!

hackathon24-white-horiz.png

2025 SAS Hackathon: There is still time!

Good news: We've extended SAS Hackathon registration until Sept. 12, so you still have time to be part of our biggest event yet – our five-year anniversary!

Register Now

How to Concatenate Values

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 4 replies
  • 1433 views
  • 0 likes
  • 4 in conversation