How to sum value from next row by group using SAS?

问题:

I want to create a column in my dataset that calculates the sum of the current row and next row for another field. There are several groups within the data, and I only want to take the sum of the next row if the next row is part of the current group. If a row is the last record for that group I want to fill with a null value.

I'm referencing reading next observation's value in current observation, but still can't figure out how to obtain the solution I need.

For example:

data have;
   input Group ID Salary;
   cards;
10 1 1
10 2 2
10 3 2
10 4 1
11 1 2
11 2 2
11 3 1
11 4 1

;
run;

The result I want to obtain here is this:

data want;
   input Group ID Salary Sum;
   cards;
10 1 1 3
10 2 2 4
10 3 2 3
10 4 1 .
11 1 2 4
11 2 2 3
11 3 1 2
11 4 1 .

;
run;

回答1:

Use BY group processing and a second SET statement that skips the first observation.

data want ;
  set have end=eof;
  by group ;
  if not eof then set have (keep=Salary rename=(Salary=Sum) firstobs=2);
  if last.group then Sum=.;
  else sum=sum(sum,salary);
run;

回答2:

Similar to Tom's answer, but using a 'look-ahead' merge (without a by statement, and firstobs=2) :

data want ;
  merge have
        have (firstobs=2 
              keep=Group Salary 
              rename=(Group=NextGroup Salary=NextSalary)) ;

  if Group = NextGroup then sum = sum(Salary,NextSalary) ;

  drop Next: ;
run ;

回答3:

I found a solution using proc expand that produced what I needed:

proc sort data = have;
    by Group ID;
run;

proc expand data=have out=want method=none;
 by Group;
 convert Salary = Next_Sal / transformout=(lead 1);
run;

data want(keep=Group ID Salary Sum);
    set want;
    Sum = Salary + Next_Sal;
run;