Calculations over rows for each customer

[Ronein]

Thank you,

Data set STATS helped and I am using it to calssify each customer to the correct group.

Thank you again

[s_lassen]

Here is another solution which is more verbose but easier to understand (I think) than the one suggested by @yabwon :

data want;                                                        
  do until(last.id);                                              
    set have;                                                     
    n_change+(score ne lag(score));  /* the LAG function must be called every time */                             
    by id notsorted;                                              
    if first.id then do;                                          
      score0=score;                                               
      n_change=0;                                                 
      end;                                                        
    end;                                                          
  select(n_change);                                               
    when(0) group=1; /* stays same */                             
    when(1) do;                                                   
      if score>score0 then group=2; /* higher value means worse */
      else group=3; /* score got better */                        
      end;                                                        
    otherwise do;                                                 
      if score>score0 then group=5; /* higher value means worse */
      else group=4; /* score got better or stayed the same */     
      end;                                                        
    end;                                                          
  keep id group n_change score score0;                            
run;

[Ksharp]

Data have;
Input ID time score $;
cards;
2 0 B
2 1 B
2 2 D
3 0 C
3 1 C
3 2 C
4 0 D
4 1 C
4 2 C
4 3 C
4 4 D
4 5 D
4 6 E
5 0 B
5 1 B
5 2 E
5 3 B
6 0 C
6 1 C
6 2 C
6 3 C
6 4 B
6 5 B
6 6 B
1 0 A
1 1 A
1 2 A
1 3 A
1 4 A
1 5 A
1 6 A
1 7 A
1 8 A
1 9 A
1 10 A
1 11 A
1 12 A
;


data want;
change=0;
do until(last.id);
 set have;
 by id score notsorted;
 if first.id then first=score;
 change+first.score;
end;

length classification $ 40;
if change=1 then classification="stay same";
else if change=2 then do; 
                        if first<score then classification='get worse';
                          else  classification='get better';
                      end;
 else if change>2 then do;
                        if first<score then classification='more than 1 change and get worse';
                          else  classification='more than 1 change stay same or better';
                      end;
keep id classification;
run;

[s_lassen]

Hi Ksharp!

Good idea just to use first.score instead of the LAG function.

Only one suggestion: If you set CHANGE=-1 in the beginning of the DATA WANT datastep, you get the actual number of changes, which I think was also one of the data originally wanted.

[Ksharp]

Agree! 🙂