Hi Everyone,
My data has id and value.
For each row[i], I want to check the window from [i] to [i+5]. I want to find the first non-missing value, say 1, and count how many 1 before -1 shows up.
If the first non-missing value is -1, I want to count how many -1 before 1 show up.
To distinguish between 1 series and -1 series, the count should have + sign for 1 and - sign for -1.
In my data below, the first row is 1, which serves as the first non-missing, thus the count is 3 (value 1)
2nd row: count = 2
3 row: count=1
4 row: count =1 as -1 shows in 7the record
My code is below and I really curious why it doesn't work.
It appears that the count is wrong for row with value =. and for the last few rows.
For simplicity, I don't introduce the condition of the same id yet.
Can you please help?
Many thanks,
HHC
data have;
input id value;
datalines;
1 1
1 .
1 1
1 .
1 1
1 .
1 -1
1 1
1 .
2 1
2 1
2 .
;run;
data want;
set have nobs=nobs;
drop i j_: out_:;
i+1;
out_j=0;
count=0;
DO j=i to i+5 until (j=nobs or out_j=1);
out_k=0;
set have (keep = id value rename=(id=j_id value=j_value)) point=j;
IF j_value>0 then do; /*checking positive value*/
do k=j to i+5 until (k=nobs or out_k=1);
set have (keep = id value rename=(id=k_id value=k_value)) point=k;
if k_value>0 then
count=count+1;
else
if k_value =-1 then do;
out_k=1;
leave;
end;
END;
End;
ELSE
IF j_value<0 then do;/*checking positive value*/
do k=j to i+5 until (k=nobs or out_k=1);
set have (keep = id value rename=(id=k_id value=k_value)) point=k;
if k_value<0 and k_value^=. then
count=count-1;
else
if k_value =1 then do;
out_k=1;
leave;
end;
END;
End;
if out_k=1 then out_j=1;
END;
run;
... View more
