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;
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.
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.
/*
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;
Thank you all for helping.
HHC
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.