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
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;
@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.
Shouldn't you also have a Date variable or do you really intend to implement logic using the row number only?
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;
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:
So simple might be good, but it depends on some of the details involved in getting the intended result.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.