Getting output from last row

[Zakir]

Hi Everyone,

I have a data set from which I want to keep some observations. First, let me give some ideas about the data set. The data set contains the following things:

Bidder             Cum_vol          Target       Dummy        ID         Penalty

ByronHal          100                  800             0                1           7000

ByronHal           250                 800             0                1           5500

ByronHal           500                 800             0                1           3000

EdyssaPal        200                 500              0                2           3000

EdyssaPal        600                 500              0                2           -1000

EdyssaPal        150                 500              1                2            3500

KellyO'L            500                 400              0                3           1000

KellyO'L            250                 400              1                3           1500

KellyO'L            300                 400              0                3            1000

KellyO'L            400                 400              0                3               0

Now, I want to keep only the last row of each ID (let's say for ID=1, so penalty will be 3000). Second, where dummy=1, I want to keep that row immediate before that dummy. Let's say for ID=2, penalty= -1000 and for ID=3, penalty= 1000. The final output should look like the following format.

Bidder             Cum_vol          Target       Dummy        ID         Penalty

ByronHal           500                 800             0                1           3000

EdyssaPal        600                 500              0                2           -1000

KellyO'L            500                 400              0                3           1000

Please help to solve this. Any help would be highly appreciated.

Thank you in advance!!

[PeterClemmensen]

If I understand your requirements correctly, then do

data have;
length Bidder $20;
input Bidder $ Cum_vol Target Dummy ID Penalty;
datalines;
ByronHal 100 800 0 1 7000
ByronHal 250 800 0 1 5500
ByronHal 500 800 0 1 3000
EdyssaPal 200 500 0 2 3000
EdyssaPal 600 500 0 2 -1000
EdyssaPal 150 500 1 2 3500
KellyO'L 500 400 0 3 1000
KellyO'L 250 400 1 3 1500
KellyO'L 300 400 0 3 1000
KellyO'L 400 400 0 3 0
;

data want;
   set have;
   by ID;
   where dummy ne 1 and penalty ne 0;
   if last.ID;
run;

[Tom · Accepted solution]

So when there is a row with DUMMY=1 you want to skip the rule that outputs the last observation?

What happens if there are multiple rows with DUMMY=1 in a group?  Do you want to output multiple rows? First one? Last one?

What happens if DUMMY=1 is the FIRST observation for a group?

Assuming that you want to output any record where the NEXT record has the DUMMY flag set and if none were found then output the last observation then you could use code like this:

data HAVE ;
  input Bidder :$10. Cum_vol Target Dummy ID Penalty ;
datalines4;
ByronHal 100 800 0 1 7000
ByronHal 250 800 0 1 5500
ByronHal 500 800 0 1 3000
EdyssaPal 200 500 0 2 3000
EdyssaPal 600 500 0 2 -1000
EdyssaPal 150 500 1 2 3500
KellyO'L 500 400 0 3 1000
KellyO'L 250 400 1 3 1500
KellyO'L 300 400 0 3 1000
KellyO'L 400 400 0 3 0
;;;;

data want ;
  do until (last.bidder);
    set have end=eof;
    by bidder ;
    if not eof then set have(firstobs=2 keep=dummy rename=(dummy=next_dummy));
    else call missing(next_dummy);
    if next_dummy and not last.bidder then do;
      output;
      any=1;
    end;
  end;
  if not any then output;
  drop any;
run;

[Zakir]

Hi Tom,

Thank you for your help. The questions you put forward are very important. I appreciate your intelligence and programming skill. I have checked your codes and found that after proc sort nodupkey,  I am getting what I wanted. I would look forward to get further help in the future. Finally, let me thank you again to resolve this!!

Kind Regards,

Zakir