topic Re: Need Data Step Solution in SAS Programming

Need Data Step Solution

Aman4SAS — Tue, 24 Jul 2018 11:31:07 GMT

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

Re: Need Data Step Solution

Kurt_Bremser — Tue, 24 Jul 2018 11:56:26 GMT

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

Re: Need Data Step Solution

SuryaKiran — Tue, 24 Jul 2018 12:20:24 GMT

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;

Re: Need Data Step Solution

Ksharp — Tue, 24 Jul 2018 12:24:30 GMT

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;

Re: Need Data Step Solution

Aman4SAS — Tue, 24 Jul 2018 13:43:49 GMT

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?

Re: Need Data Step Solution

Kurt_Bremser — Tue, 24 Jul 2018 13:59:16 GMT

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

Re: Need Data Step Solution

ballardw — Tue, 24 Jul 2018 14:15:08 GMT

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