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!

sas-innovate-2024.png

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.

 

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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