topic Re: Getting output from last row in SAS Data Management

Getting output from last row

Zakir — Tue, 04 Sep 2018 08:32:24 GMT

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!!

Re: Getting output from last row

PeterClemmensen — Tue, 04 Sep 2018 14:25:27 GMT

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;

Re: Getting output from last row

Tom — Tue, 04 Sep 2018 14:39:17 GMT

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;

Re: Getting output from last row

Zakir — Wed, 05 Sep 2018 03:32:43 GMT

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