comapre previous rows and keep or drop

[ger15xxhcker]

Hi!

How can i solve this with sas data step?

What i want:

if 1. row (id=782139) and  -64803 < prev row amt (-43504) then keep the row else drop ... and so.

Thanks for your help!

DATA work.sample;
	INPUT ID DATE AMT;
	DATALINES; 
	782139	20191025	-64803
	782139	20190925	-43504
	782139	20190913	-21775
	782139	20190826	-44775
	782139	20190725	-22620
	782138	20191025	-99803
	782138	20190925	-43504
	782138	20190913	-21775
	782138	20190826	-10775
	782139	20190725	-22620
;
RUN;

DATA work.want;
	INPUT ID DATE AMT;
	DATALINES; 
	782139	20191025	-64803
	782139	20190925	-43504
	782139	20190913	-21775
	782138	20191025	-99803
	782138	20190925	-43504
	782138	20190913	-21775
	782138	20190826	-10775
;
RUN;

[PeterClemmensen]

One way

data want;
   array a{999999} _temporary_;
   array b{999999} _temporary_;

   set sample;

   if amt > a[id] & b[id] ne 1 then do;
      output;
      a[id]=amt;
   end;
   else b[id]=1;
run;

Result:

ID      DATE        AMT 
782139  25/10/2019  -64803 
782139  25/09/2019  -43504 
782139  13/09/2019  -21775 
782138  25/10/2019  -99803 
782138  25/09/2019  -43504 
782138  13/09/2019  -21775 
782138  26/08/2019  -10775 

[Tom · Accepted solution]

Your description of the rule does not match the results you want.

First it looks like you want to keep the observation where the value is GREATER than the previous value since all of your values are negative. And also you are keeping the first observation per ID.

So why do you not keep the last observation for ID=782139 ?  Its value of -22,620 is greater than the value on the previous observation.

Are you instead checking for the changes in the maximum value for the id?

Can we assume the data is sorted by ID? Your example data is not sorted. Your result data is sort be DESCENDING values of ID.

proc sort data=sample;
  by descending id ;
run;

data want;
  set sample;
  by descending id;
  if first.id or amt > max_amt then do;
    max_amt=amt;
    output;
  end;
  retain max_amt;
run;