Barite | Level 11

## Count number of negative and positive within a window

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.

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;``````
1 ACCEPTED SOLUTION

Accepted Solutions
Super User

## Re: Count number of negative and positive within a window

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.

3 REPLIES 3
Super User

## Re: Count number of negative and positive within a window

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.

Super User

## Re: Count number of negative and positive within a window

``````/*
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();
_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;``````
Barite | Level 11

## Re: Count number of negative and positive within a window

Thank you all for helping.

HHC

Discussion stats
• 3 replies
• 410 views
• 2 likes
• 3 in conversation