Count number of negative and positive within a window

[hhchenfx]

Hi,

My code below works but clearly if I the lag value is 90 Or when I want to change the lag value, it will cause a lot of trouble.

The idea is look back N records and count the number of -1 and 1.

Can you please help to write a better code?

Thanks,

HHC

data have;
input date id value;
datalines;
1 1 1
2 1 0
3 1 -1
4 1 0
5 1 -1
6 1 -1
1 2 0
2 2 1
3 2 0
;run;


data want; set have;
drop l1-l3 i;
l1=lag1(value);
l2=lag2(value);
l3=lag3(value);

array l(*) 	l1 l2 l3 ;
count_negative=0;
count_positive=0;
do i=1 to dim(l);
	if l{i}=-1 then count_negative=count_negative+1;
	else
	if l{i}=1 then count_positive=count_positive+1;

end;

run;

[Kurt_Bremser · Accepted solution]

Use a temporary array:

data have;
input date id value;
datalines;
1 1 1
2 1 0
3 1 -1
4 1 0
5 1 -1
6 1 -1
1 2 0
2 2 1
3 2 0
;

%let window = 3;

data want;
set have;
by id;
array win {&window.} _temporary_;
if first.id
then do i = 1 to &window.;
  win{i} = .;
end;
win{mod(_n_,&window.) + 1} = value;
count_negative = 0;
count_positive = 0;
do i = 1 to &window.;
  if win{i} = -1 then count_negative + 1;
  else if win{i} = 1 then count_positive + 1;
end;
drop i;
run;

The macro variable determines how far you want to look back.

[Ksharp]

/*
Assuming there is no gap between two date.
*/
data have;
input date id value;
datalines;
1 1 1
2 1 0
3 1 -1
4 1 0
5 1 -1
6 1 -1
1 2 0
2 2 1
3 2 0
;

%let n=3;
data want;
if _n_=1 then do;
  declare hash h();
  h.definekey('date');
  h.definedata('value');
  h.definedone();
end;
set have;
by id;
if first.id then h.clear();
h.add();
_value=value;
count_negative=0;
count_positive=0;
do _date=date-1 to date-&n. by -1;
  if h.find(key:_date)=0 then do;
    if value=-1 then count_negative+1;
 if value= 1 then count_positive+1;
  end;
end; 
value=_value;
drop _value _date;
run;

[hhchenfx]

Thank you all for helping.

HHC