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
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;
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).
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;
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;
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?
@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.
@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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.