CODEWITHSUNDEEP
sas
user2146441
user2146441

Reputation: 228

SAS DATA STEP: Calculation based on previous row/next row

I have the following sample data:

data have;
input username $  betdate : datetime. stake;
dateOnly = datepart(betdate) ;
format betdate DATETIME.;
format dateOnly ddmmyy8.;
datalines; 
player1 12NOV2008:12:04:01 90
player1 04NOV2008:09:03:44 30
player2 07NOV2008:14:03:33 120
player1 05NOV2008:09:00:00 50
player1 05NOV2008:09:05:00 30
player1 05NOV2008:09:00:05 20
player2 09NOV2008:10:05:10 10
player2 15NOV2008:15:05:33 35
player1 15NOV2008:15:05:33 35
player1 15NOV2008:15:05:33 35
run;
proc print;run;

proc sort data=have; by username dateonly betdate; run;
data want;
set have;
by username dateonly betdate;   
if first.username then calendarTime = 0;
if first.dateonly then calendarTime + 1;
if first.username then eventTime = 0;
if first.betdate then eventTime + 1;
previousBetdateForPlayer = .;
nextBetdateForPlayer = .;
run;
proc print;run;

How can I fill in the values for the variables 'previousBetdateForPlayer' and 'nextBetdateForPlayer'. I know how to do it PROC SQL, but was hoping there was a more efficient way to do it with a DATA STEP.

Both of these variable need to be calculated for each username, so the value of 'previousBetdateForPlayer' will be NULL for each username's first bet. The value of 'nextBetdateForPlayer' will be NULL for each username's final bet.

Upvotes: 0

Views: 1939

Answers (1)

Dmitry Shopin
Dmitry Shopin

Reputation: 1763

data want; 
  set have; 

  /*join right the same dataset shifted 1 row up*/
  if not eof then do;
    set have(firstobs=2
                    keep=betdate 
                     rename=(betdate=nextBetdateForPlayer)) end=eof;

  end;

    by username dateonly betdate;
    if first.username then calendarTime = 0; 
    if first.dateonly then calendarTime + 1;
    if first.username then eventTime = 0;
    if first.betdate then eventTime + 1; 

   /*add previous and nullify it if first bet*/
    previousBetdateForPlayer = lag(previousBetdateForPlayer); 
    if first.username then previousBetdateForPlayer=.;

    /*nullify next if  last bet*/
    if last.username then nextBetdateForPlayer = .;
 run;

Upvotes: 2

Related Questions