Next value by group in SAS

Question

I am trying to create a new variable that takes the next value in a group. In particular, I have:

User   Quarter
 A     2012Q1
 A     2012Q2
 A     2012Q3

My desired output would be:

User   Quarter  Next_Quarter
 A     2012Q1    2012Q2
 A     2012Q2    2012Q3
 A     2012Q3     n/a

I have tried to have a counter for each customer, but I am not sure where I should go from there...

Answer 1

There is no an easy way to look forward in a data step. What you can do is reverse the order, use the lag function to get the value, and reorder appropriately.

data have;
input User $  Quarter $;
datalines;
A     2012Q1
A     2012Q2
A     2012Q3
B     2012Q1
B     2012Q2
B     2012Q3
;

/*Reverse the order*/
proc sort data=have;
by user descending quarter;
run;

/*Use Lag() to get the previous value (next since we reversed it)*/
data want;
set have;
by user;

tmp = lag(quarter);

if ^first.user then
    Next_Quarter = tmp;
else
    Next_Quarter = "n/a";

drop tmp;
run;

/*Re-sort to the proper order*/
proc sort data=want;
by user quarter;
run;

Answer 2

You can use an extra SET statement with FIRSTOBS=2 to "look ahead". Make sure to account for any BY groups.

data want ;
  set have end=eof ;
  by user;
  if not eof then set have(keep=Quarter rename=(Quarter=Next_Quarter) firstobs=2) ;
  if last.user then call missing(Next_Quarter);
run;

If you do not have BY groups then change the last IF to

  if eof then call missing(Next_Quarter);