topic Re: Count number of negative and positive within a window in SAS Programming

Count number of negative and positive within a window

hhchenfx — Fri, 24 Feb 2023 03:47:58 GMT

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;

Re: Count number of negative and positive within a window

Kurt_Bremser — Fri, 24 Feb 2023 06:05:39 GMT

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.

Re: Count number of negative and positive within a window

Ksharp — Fri, 24 Feb 2023 12:04:55 GMT

/*
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;

Re: Count number of negative and positive within a window

hhchenfx — Fri, 24 Feb 2023 19:54:56 GMT

Thank you all for helping.

HHC