BookmarkSubscribeRSS Feed
hhchenfx
Rhodochrosite | Level 12

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;

 

2 REPLIES 2
PGStats
Opal | Level 21

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;

PGStats_0-1664480464165.png

 

PG
hhchenfx
Rhodochrosite | Level 12

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

hackathon24-white-horiz.png

The 2025 SAS Hackathon has begun!

It's finally time to hack! Remember to visit the SAS Hacker's Hub regularly for news and updates.

Latest Updates

How to Concatenate Values

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 2 replies
  • 746 views
  • 1 like
  • 2 in conversation