Count numbers of the same value with sequence

[hhchenfx]

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:

1^st series_value is 500 and has 2 counts and the SUM_series1 is 1000

2^nd 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

4^th series_value is -500 and has 1 count and the SUM_series4 is -500

5^th 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 5^th series.

So the output data should have following column (number in parenthesis is value of each column for 1^st 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;

[PeterClemmensen]

A few questions:

• Do you want to do this for each 12 obs window in the data?
• Do you really want the value in the desired output to be series1_value(500) ? Why not just 500?

Regards Peter.

[hhchenfx]

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

[hhchenfx · Accepted solution]

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;