Search code examples
sasanalytics

Getting next observation per group

I am working on a dataset in SAS to get the next observation's score should be the current observation's value for the column Next_Row_score. If there is no next observation then the current observation's value for the column Next_Row_score should be 'null'per group(ID). For better illustration i have provided the sample below dataset : 

ID Score 
10 1000 
10 1500 
10 2000 
20 3000 
20 4000 
30 2500

Resultant output should be like - 

ID Salary Next_Row_Salary 
10 1000   1500 
10 1500   2000 
10 2000   . 
20 3000   4000 
20 4000   .  
30 2500   2500

Thank you in advance for your help.

Solution 
  • data want(drop=_: flag);
   merge have have(firstobs=2 rename=(ID=_ID Score=_Score));
   if ID=_ID then do;
       Next_Row_Salary=_Score;
       flag+1;
   end;
   else if ID^=_ID and flag>=1 then do;
           Next_Row_Salary=.;
           flag=.;
   end;
   else Next_Row_Salary=score;
run;