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
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.