How to find last negative value of a column in a sas dataset

[SnehasisTTPL]

Dear All,

How can I select all positive Balance till first occurence of a negative Balance of a particular ID and Order should be last to first.

I have a data set like below:

ID	Balance
AADDSEESADD	4490
AADDSEESADD	23185
AADDSEESADD	10210415
AADDSEESADD	-30883186
AADDSEESADD	-30893858
AADDSEESADD	-31206302
AADDSEESADD	6574160
AADDSEESADD	6549160
AADDSEESADD	6539160
EEDDSEESGFH	4325234532
EEDDSEESGFH	-5476
EEDDSEESGFH	-50
EEDDSEESGFH	3722
EEDDSEESGFH	27755

OUTPUT want like below:

ID	Balance
AADDSEESADD	6574160
AADDSEESADD	6549160
AADDSEESADD	6539160
EEDDSEESGFH	3722
EEDDSEESGFH	27755

Regards

Snehasis

[RW9]

Post test data in the form of a datastep.  As such not tested this:

data want;
  set have;
  by id;
  retain flag;
  if first.id then flag=0;
  if balance > 0 and lag(balance < 0) then flag=1;
  if flag=1 then output;
run; 

[Kurt_Bremser]

@RW9's code will need a small extension to cope with cases where the last balance of an id is negative and the first balance of the following id is positive.

[Patrick]

@@SnehasisTTPL

Shouldn't you also have a Date variable or do you really intend to implement logic using the row number only?

[Ksharp]

data have;
input ID : $20. Balance;
sign=sign(Balance);
cards;
AADDSEESADD	4490
AADDSEESADD	23185
AADDSEESADD	10210415
AADDSEESADD	-30883186
AADDSEESADD	-30893858
AADDSEESADD	-31206302
AADDSEESADD	6574160
AADDSEESADD	6549160
AADDSEESADD	6539160
EEDDSEESGFH	4325234532
EEDDSEESGFH	-5476
EEDDSEESGFH	-50
EEDDSEESGFH	3722
EEDDSEESGFH	27755
;
run;
data want;
 do until(last.sign);
   set have;
   by id sign notsorted;
 end;
yes=last.id;
 do until(last.sign);
   set have;
   by id sign notsorted;
   if yes then output;
 end;
drop yes sign;
run;

[Astounding]

Perhaps a little simpler:

data want;

set have;

by ID;

if first.ID then output_flag = 'N';

if (balance < 0 then output_flag = 'Y';

retain output_flag;

if output_flag = 'Y' and balance >= 0;

run;

However, there are always cases to consider:

• Could there be missing values for BALANCE?
• For the same ID, could there be a group of negative values, followed by a group of positive values, followed by another group of negative values?  (What should happen then?)

So simple might be good, but it depends on some of the details involved in getting the intended result.