每组下一次观察

Question

我正在使用SAS中的dataset来获取下一个观察的得分，该值应该是列Next_Row_score的当前观测值。如果没有下一个观察，那么列Next_Row_score的当前观测值应该是‘null’/组(ID)。为了更好地说明，我提供了以下数据集的示例：

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

结果的产出应该是-

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

提前谢谢你的帮助。

Answer 1

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;

Answer 2

试试这个：

data have;
  input ID Score;

  datalines;
10 1000 
10 1500 
10 2000 
20 3000 
20 4000 
30 2500 
;
run;

proc sql noprint;
    select count(*)  into :obsHave
    from have;
quit;

data want2(rename=(id1=ID Score1=Salary) drop=ID id2 Score);
    do i=1 to &obsHave;
        set have point=i;
        id1=ID;
        Score1=Score;
        j=i+1;
        set have point=j;
        id2=ID;
        if id1=id2 then do;
            Next_Row_Salary = Score;
        end;
        else Next_Row_Salary=".";
        output;
    end;
stop;
;
run;

Answer 3

有一种更简单的(至少在我看来) proc方法不涉及循环：

data have;
  input ID Score;
  datalines;
10 1000 
10 1500 
10 2000 
20 3000 
20 4000 
30 2500 
;
run;

/*count each observation's place in its ID group*/
data have2;
    set have;
    count + 1;
    by id;
    if first.id then count = 1;
run;

/*if there is only one ID in a group, keep original score, else lag by 1*/
proc sql;
    create table want as select distinct
        a.id, a.score,
        case when max(a.count) = 1 then a.score else b.score end as score2
        from have2 as a
        left join have2 (where = (count > 1)) as b
        on a.id = b.id and a.count = b.count - 1
        group by a.id;
quit;