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.
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.