Need Data Step Solution

[Aman4SAS]

Dear Friends,

I need to help to find the solution for my problem.(If its possible in SAS)

Data 

Acc_no  date  balances

101    1     2000

101    2     3000

101    3     0

101    4     -200 This date required

101    5    -350

101    6    1000

101   7      5000

101  8      8000

101  9     -200 This date required

101  10    -500

101   11   -1000

101   12    -1500

101   13   -2000

like this i have multiple accounts i need latest date when balances start being negative. I have ticked on the date which required.

Thanks in advance

[Kurt_Bremser]

Use by-group processing for the accounts, a retained variable, and set it everytime lag(balances) is positive and balances is negative.

For code, please provide your example data in a readily usable form (data step with datalines).

[SuryaKiran]

Hope this helps:

data have;
infile datalines dsd dlm=',' missover;
input Acc_no  date  balances;
datalines;
101,1,2000
101,2,3000
101,3,0
101,4,-200
101,5,-350
101,6,1000
101,7,5000
101,8,8000
101,9,-200
101,10,-500
101,11,-1000
101,12,-1500
101,13,-2000
;
run;
proc sort data=have;
by Acc_no  date;
run;

data want;
set have;
Lag_Bal=lag(balances);
if balances<0 and lag_Bal>=0 ;
run;

[Ksharp · Accepted solution]

data have;
input Acc_no  date  balances;
cards;
101    1     2000
101    2     3000
101    3     0
101    4     -200 This date required
101    5    -350
101    6    1000
101   7      5000
101  8      8000
101  9     -200 This date required
101  10    -500
101   11   -1000
101   12    -1500
101   13   -2000
;
run;
data temp;
 set have;
 sign=sign(balances);
run;
data want;
 set temp;
 by acc_no sign notsorted;
 if sign=-1 and first.sign;
run;

[Aman4SAS]

Thanks a lot Sir, One question on your ans. if there is other numerical variable which contains negative and positive values then how your code will recognize that we need work on balance?

[Kurt_Bremser]

---

@Aman4SAS wrote:

Thanks a lot Sir, One question on your ans. if there is other numerical variable which contains negative and positive values then how your code will recognize that we need work on balance?

---

The crucial step is this one:

data temp;
 set have;
 sign=sign(balances);
run;

Here, sign is derived from balances, and nothing else.

[ballardw]

---

@Aman4SAS wrote:

Thanks a lot Sir, One question on your ans. if there is other numerical variable which contains negative and positive values then how your code will recognize that we need work on balance?

---

It is somewhat poor form to start adding additional requirements after working solutions have been provided. You would have to provide additional example data, descriptions of all of the rules involved and example of desired output.

You provide no data and no actual rules. You have to provide exactly how "other numerical variable" values impact the entire problem. Depending on your actual additional rules the initial provided solutions may require little modification or drastically more coding, but without details it is hard to say.