Count number of occurrence with sequence

[hhchenfx]

Hi Everyone,

My data has ID and TT.

For each row and within certain lookback window, I want to identify the number of time each number in TT show up.

For the first row, we can see that the first set of TT value is 50 and it show up 2 time.

After that, value 3 shows up for 2 times

Then value -500 shows up for 1 time.

I keep the value and the time in 2 set of variables: value_series_i and N_series_i

My code below use nested loop and it work correctly when I track series_1 and series_2.

When I copy the code and add series_3, the results mess up. Somehow, it jump to last valid row and use this information for both series_2 and series_3.

For reference purpose, for the first row: 

N_series_1: 2

Value_series_1: 500

N_series_2: 2

Value_series_2: 3

N_series_3: 1

Value_series_3: -500

I have spent several hours to clean the code and now truly have no clue of how to fix it and really appreciate if you could help.

Thanks,

HHC

data have;
input ID  TT;
datalines;
1 500
1 500
1 3
1 3
1 -500
1 -3

;run;

%let lookback=4;


*THIS CODE WORK CORRECTLY;
data want2;
set have nobs=nobs;
drop i k l End_run ID1-ID5 TT1-TT5 end_:;
i+1;
End_=0;
N_series_1 = 0; value_series_1 =0;

*series 1----;
N_series_1 = 1; value_series_1 =TT;
DO j=i+1 to min(nobs,i+&lookback) until (End_=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 do; 
*series 2 when value change---;
	N_series_2=1;value_series_2=TT1;
	DO k=j+1 to min(nobs,i+&lookback) until (End_=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 do; 
		End_=1;
		end;
END;END;END;
run;

*THIS CODE DOESN'T WORK;
data want3;
set have nobs=nobs;
drop i k l End_run ID1-ID5 TT1-TT5 end_:;
i+1;
End_=0;
N_series_1 = 0; value_series_1 =0;

*series 1----;
N_series_1 = 1; value_series_1 =TT;
DO j=i+1 to min(nobs,i+&lookback) until (End_=1);
	set have(keep = ID TT rename =(ID=ID1 TT=TT1)) point=j;
	if TT1=value_series_1 then N_series_1=N_series_1+1;
	else do; 
*series 2 when value change---;
	N_series_2=1;value_series_2=TT1;
	DO k=j+1 to min(nobs,i+&lookback) until (End_=1);
		set have(keep = ID TT rename =(ID=ID2 TT=TT2)) point=k;
		if TT2=value_series_2 then N_series_2=N_series_2+1;	 
		else do; 
*series 3 when value change---;
		N_series_3=1;value_series_3=TT2;
		DO l=k+1 to min(nobs,i+&lookback) until (End_=1);
			set have(keep = ID TT rename =(ID=ID3 TT=TT3)) point=l;
			if TT3=value_series_3 then N_series_3=N_series_3+1;
			 
			else do; 
		*exit all;
			End_=1;end;

END;END;END;END;END;
run;

[PGStats]

You didn't show us the expected result, but maybe this?

data temp;
length var $16;
do i = 1 by 1 until(last.id);
    do n = 1 by 1 until(last.tt);
        set have; by id tt notsorted;
        end;
    var = cats("N_series_", i); value = n; output;
    var = cats("value_series_", i); value = tt; output;
    end;
keep id var value;
run;

proc transpose data=temp out=want(drop=_name_);
var value;
id var;
by id;
run;

[hhchenfx]

It is exactly the idea. 

I want to do for all rows and the loop stop when (lookback up reach 10 rows or series reach to 6, whatever come first.

HHC