topic Re: How to find last negative value of a column in a sas dataset in SAS Programming

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

SnehasisTTPL — Wed, 16 May 2018 10:13:50 GMT

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

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

RW9 — Wed, 16 May 2018 10:22:54 GMT

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;

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

Kurt_Bremser — Wed, 16 May 2018 10:52:07 GMT

@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.

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

Patrick — Wed, 16 May 2018 11:55:37 GMT

@@SnehasisTTPL

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

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

Ksharp — Wed, 16 May 2018 12:34:31 GMT

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;

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

Astounding — Wed, 16 May 2018 14:12:39 GMT

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.