Date:         Tue, 5 Jul 2011 01:16:52 -0400
Reply-To:     Søren Lassen <s.lassen@POST.TELE.DK>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Søren Lassen <s.lassen@POST.TELE.DK>
Subject:      Re: Setting column value by looking backward in dataset
Comments: To: Ben Ewing <ben.r.ewing@GMAIL.COM>
Content-Type: text/plain; charset=ISO-8859-1

Ben,
Lag functions can be kind of tricky, expecially if you want to look back
a variable number of records. Here is a simple solution that does not
use lag:

data want;
  do until (last.unit);
    set unit_status;
    by unit status notsorted;
    output;
    if last.status then
      prev_status=status;
    end;
run;

Regards,
Søren

On Mon, 4 Jul 2011 10:29:19 -0400, Ben Ewing <ben.r.ewing@GMAIL.COM> wrote:

>Hi-
>
>I'm trying to add a column to a dataset that is dependent on a value from a
>previous record within the dataset.
>
>Given the following input data:
>
>data unit_status;
>   input unit $ date date9. hour status $;
>   datalines;
>333 01apr2009 1 OFF
>333 01apr2009 2 OFF
>333 01apr2009 3 RAMP
>333 01apr2009 4 RAMP
>333 01apr2009 5 RAMP
>333 01apr2009 6 ON
>333 01apr2009 7 ON
>333 01apr2009 8 ON
>333 01apr2009 9 RAMP
>333 01apr2009 10 RAMP
>333 01apr2009 11 ON
>333 01apr2009 12 OFF
>
>334 01apr2009 1 OFF
>334 01apr2009 2 OFF
>334 01apr2009 3 OFF
>334 01apr2009 4 RAMP
>334 01apr2009 5 RAMP
>334 01apr2009 6 ON
>334 01apr2009 7 ON
>334 01apr2009 8 ON
>334 01apr2009 9 RAMP
>334 01apr2009 10 ON
>334 01apr2009 11 ON
>334 01apr2009 12 RAMP
>;
>
>I would like to add another column called prev_status which will hold the
>status of each unit prior to (and distinct from) its current status.  This
>would involve:
>
>1.Checking the current status (i.e., RAMP)
>
>2.Look at the status from the prior record.  If the prior status is
>different from the current status (i.e. OFF ne RAMP) then save the prior
>status in the prev_status column (prev_status = OFF).
>
>3.If the prior status is the same as the current status, step back another
>record and check again for inequality.
>
>4. If looking at the first record for a specific unit, prev_stat = null.
>
>I believe this should use some form of iteration of the lagn() function
>along with the ifc() function.  Unfortunately, I've been unable to
>successfully accomplish this.
>
>Thanks for any help you can offer!
>
>Ben
>
>Desired Output:
>data unit_status_2;
>   input unit $ date date9. hour status $ prev_status $;
>   datalines;
>333 01apr2009 1 OFF  .
>333 01apr2009 2 OFF  .
>333 01apr2009 3 RAMP OFF
>333 01apr2009 4 RAMP OFF
>333 01apr2009 5 RAMP OFF
>333 01apr2009 6 ON   RAMP
>333 01apr2009 7 ON   RAMP
>333 01apr2009 8 ON   RAMP
>333 01apr2009 9 RAMP ON
>333 01apr2009 10 RAMP ON
>333 01apr2009 11 ON   RAMP
>333 01apr2009 12 OFF  ON
>
>334 01apr2009 1 OFF   .
>334 01apr2009 2 OFF   .
>334 01apr2009 3 RAMP  OFF
>334 01apr2009 4 RAMP  OFF
>334 01apr2009 5 RAMP  OFF
>334 01apr2009 6 ON    RAMP
>334 01apr2009 7 ON    RAMP
>334 01apr2009 8 ON    RAMP
>334 01apr2009 9 RAMP  ON
>334 01apr2009 10 RAMP ON
>334 01apr2009 11 ON   RAMP
>334 01apr2009 12 OFF  ON
>;