DATA Step, Macro, Functions and more

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

Reply
Occasional Contributor
Posts: 11

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

[ Edited ]

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

Super User
Super User
Posts: 9,406

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

Posted in reply to SnehasisTTPL

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;
Super User
Posts: 9,886

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

Posted in reply to SnehasisTTPL

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

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Respected Advisor
Posts: 4,672

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

Posted in reply to SnehasisTTPL

@@SnehasisTTPL

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

Super User
Posts: 10,686

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

Posted in reply to SnehasisTTPL
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;
Super User
Posts: 6,629

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

Posted in reply to SnehasisTTPL

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.

Ask a Question
Discussion stats
  • 5 replies
  • 86 views
  • 0 likes
  • 6 in conversation