Hi Everyone,
I have data of 2 columns ID and T.
T have value of -500, -3, 0, 3, 500.
For a give row, I want to look back, say 12 rows and “count how many of T having the same Non-Zero value Before T change to a different non-zero value”.
In the data below, for the 1 row I see:
1st series_value is 500 and has 2 counts and the SUM_series1 is 1000
2nd series_value is 3 and has 3 counts and the SUM_series2 is 9
3rd series_value is -3 and has 2 counts and the SUM_series3 is -6
4th series_value is -500 and has 1 count and the SUM_series4 is -500
5th series_value is 500 and has 1 count and the SUM_series5 is 500
In the count, clearly 0 can be removed but I still need it to keep the track of the looking back.
I only need up to 5th series.
So the output data should have following column (number in parenthesis is value of each column for 1st row which associated with above explanation) :
ID T
series1_value(500) SUM_series1 (1000)
series2_value (3) SUM_series2 (9)
series3_value (-3) SUM_series3 (-6)
series4_value (-500) SUM_series4 (-500)
series5_value (500) SUM_series5 (500)
Can you please help me with that?
Thank you so much.
HHC
data have;
input ID T;
datalines;
1 0
1 500
1 500
1 0
1 3
1 0
1 0
1 3
1 3
1 -3
1 0
1 -3
1 -500
1 0
1 0
1 500
1 -5
1 -500
1 0
2 0
2 3
2 3
2 0
;run;
So I do nested loop to count the series of number (TT) with the same value.
The basic idea is:
Start with 1st row. Keep TT value in value_series_1
.
Loop to next row, if TT is the same as value_series_1
increase the N_series_1
by 1. If not the same, it is the start of value_series_2
My code below work with lookback=12. It gives correct result for the first few rows but then at rows 8, number look weird with the N_series inflated to 30+.
Same thing when lookback=4, the error show right on the first row.
Look like the script doesn't stop at i+&lookback
DO j=i+1 to i+&lookback until (End_run=1);
I can't find the problem and really appreciate it if you could help.
Thanks,
HHC
data have;
input ID TT;
datalines;
1 500
1 500
1 3
1 3
1 3
1 -3
1 -3
1 -500
1 500
1 -5
1 -500
1 3
1 3
;run;
%let lookback=5;
data want;
set have;
drop i End_run ID1-ID5 TT1-TT5;
i+1;
End_run=0;
N_series_1 = 0; value_series_1 =0;
N_series_2 = 0; value_series_2 =0;
N_series_3 = 0; value_series_3 =0;
N_series_4 = 0; value_series_4 =0;
N_series_5 = 0; value_series_5 =0;
N_series_1 = 1; value_series_1 =TT;
DO j=i+1 to i+&lookback until (End_run=1);
set have(keep = ID TT rename =(ID=ID1 TT=TT1)) point=j nobs=nobs1;
if TT1=value_series_1 then N_series_1=N_series_1+1;
else
if TT1^=value_series_1 then do;
N_series_2=N_series_2+1; value_series_2=TT1;
DO k=j+1 to i+&lookback until (End_run=1);
set have(keep = ID TT rename =(ID=ID2 TT=TT2)) point=k nobs=nobs2;
if TT2=value_series_2 then N_series_2=N_series_2+1;
else
if TT2^=value_series_2 then do;
N_series_3=N_series_3+1; value_series_3=TT2;
DO l=k+1 to i+&lookback until (End_run=1);
set have(keep = ID TT rename =(ID=ID3 TT=TT3)) point=l nobs=nobs3;
if TT3=value_series_3 then N_series_3=N_series_3+1;
else
if TT3^=value_series_3 then do;
N_series_4=N_series_4+1; value_series_4=TT3;
DO m=l+1 to i+&lookback until (End_run=1);
set have(keep = ID TT rename =(ID=ID4 TT=TT4)) point=m nobs=nobs4;
if TT4=value_series_4 then N_series_4=N_series_4+1;
else
if TT4^=value_series_4 then do;
N_series_5=N_series_5+1; value_series_5=TT4;
DO n=m+1 to i+&lookback until (End_run=1);
set have(keep = ID TT rename =(ID=ID5 TT=TT5)) point=n nobs=nobs5;
if TT5=value_series_5 then N_series_5=N_series_5+1;
else if TT5^=value_series_5 then do;
End_run=1;end;
END;END;END;END;;END;END;END;END;END;
run;
A few questions:
Regards Peter.
Hi,
I want to do it for all rows in the table.
The column is series1_valueand the value in this column for the first row is 500.
Thanks,
HHC
So I do nested loop to count the series of number (TT) with the same value.
The basic idea is:
Start with 1st row. Keep TT value in value_series_1
.
Loop to next row, if TT is the same as value_series_1
increase the N_series_1
by 1. If not the same, it is the start of value_series_2
My code below work with lookback=12. It gives correct result for the first few rows but then at rows 8, number look weird with the N_series inflated to 30+.
Same thing when lookback=4, the error show right on the first row.
Look like the script doesn't stop at i+&lookback
DO j=i+1 to i+&lookback until (End_run=1);
I can't find the problem and really appreciate it if you could help.
Thanks,
HHC
data have;
input ID TT;
datalines;
1 500
1 500
1 3
1 3
1 3
1 -3
1 -3
1 -500
1 500
1 -5
1 -500
1 3
1 3
;run;
%let lookback=5;
data want;
set have;
drop i End_run ID1-ID5 TT1-TT5;
i+1;
End_run=0;
N_series_1 = 0; value_series_1 =0;
N_series_2 = 0; value_series_2 =0;
N_series_3 = 0; value_series_3 =0;
N_series_4 = 0; value_series_4 =0;
N_series_5 = 0; value_series_5 =0;
N_series_1 = 1; value_series_1 =TT;
DO j=i+1 to i+&lookback until (End_run=1);
set have(keep = ID TT rename =(ID=ID1 TT=TT1)) point=j nobs=nobs1;
if TT1=value_series_1 then N_series_1=N_series_1+1;
else
if TT1^=value_series_1 then do;
N_series_2=N_series_2+1; value_series_2=TT1;
DO k=j+1 to i+&lookback until (End_run=1);
set have(keep = ID TT rename =(ID=ID2 TT=TT2)) point=k nobs=nobs2;
if TT2=value_series_2 then N_series_2=N_series_2+1;
else
if TT2^=value_series_2 then do;
N_series_3=N_series_3+1; value_series_3=TT2;
DO l=k+1 to i+&lookback until (End_run=1);
set have(keep = ID TT rename =(ID=ID3 TT=TT3)) point=l nobs=nobs3;
if TT3=value_series_3 then N_series_3=N_series_3+1;
else
if TT3^=value_series_3 then do;
N_series_4=N_series_4+1; value_series_4=TT3;
DO m=l+1 to i+&lookback until (End_run=1);
set have(keep = ID TT rename =(ID=ID4 TT=TT4)) point=m nobs=nobs4;
if TT4=value_series_4 then N_series_4=N_series_4+1;
else
if TT4^=value_series_4 then do;
N_series_5=N_series_5+1; value_series_5=TT4;
DO n=m+1 to i+&lookback until (End_run=1);
set have(keep = ID TT rename =(ID=ID5 TT=TT5)) point=n nobs=nobs5;
if TT5=value_series_5 then N_series_5=N_series_5+1;
else if TT5^=value_series_5 then do;
End_run=1;end;
END;END;END;END;;END;END;END;END;END;
run;
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.