I have a dataset and i need to flag the sum their delinquency period and after that i need sum of each delinquent period.
data have;
infile datalines;
input id_acc acc_no status_code $5. info_date date9. act_bal total_debt total_credit ;
format info_date date9.;
datalines;
1111 999585548 D1A 30Jun2007 500 1000 2000
1111 999585548 AAA 31Jul2007 500 1000 2000
1111 999585544 D1A 31Aug2007 400 5000 4600
1111 999585547 D2A 30Sep2007 500 1000 2000
1111 999585540 D1D 31Oct2007 300 1000 2000
1111 999585544 AAA 30Nov2007 500 1300 2000
1111 999585547 AAA 31Dec2007 500 1000 2000
1111 999585540 D1D 31Jan2008 500 1000 2000
1111 999585544 D2D 28Feb2008 200 1800 2000
1111 999585547 D3D 31Mar2008 500 1000 2000
1111 999585540 D4D 30Apr2008 500 1000 2000
1111 999585540 D5D 31May2008 500 1000 2000
1111 999585540 AAA 30Jun2008 500 1000 2000
1111 999585540 AAA 31Jul2008 100 2000 3000
1111 999595540 D1D 31Aug2008 200 1000 2000
1111 999587540 LEP 30Sep2008 500 1000 2000
1111 999587540 LEP 31Oct2008 500 1000 2000
1111 999585540 LEP 30Nov2008 500 1000 2000
1112 999505548 D2A 30Jun2007 500 1000 2000
1112 999505548 AAA 31Jul2007 400 1000 2000
1112 999505544 D1A 31Aug2007 500 1000 2000
1112 999505547 D2A 30Sep2007 500 1000 2000
1112 999505540 D1D 31Oct2007 500 1000 2000
1112 999505544 D2D 30Nov2007 500 1000 2000
1112 999505547 D3D 31Dec2007 700 1000 2000
1112 999505540 D4D 31Jan2008 500 1000 2000
1112 999505544 D5D 28Feb2008 500 1000 2000
1112 999505547 D6D 31Mar2008 500 1000 2000
1112 999505540 LEP 30Apr2008 500 1000 2000
1112 999505540 LEP 31May2008 800 1000 2000
1112 999505540 LEP 30Jun2008 500 1000 2000
1112 999505540 LEP 31Jul2008 500 1000 2000
1112 999505540 LEP 31Aug2008 500 1000 2000
1112 999505540 LEP 31Oct2008 900 1000 2000
1112 999505540 LEP 30Nov2008 500 1000 2000
;
run;
proc print;
run;
The code i am using is:
proc sort data=have;
by id_acc info_date;
run;
%let del =_n_;
data second;
set have;
if first.id_acc=1 and status_code in ('D1A','D2A','D1D','D2D','D3D','D4D','D5D','D6D') THEN do;
balance_at_def=act_bal;
delinquey=1;
end;
if first.id_acc=0 and status_code in ('D1A','D2A','D1D','D2D','D3D','D4D','D5D','D6D') THEN do;
balance_at_def=act_bal;
delinquey=&del;
end;
by id_acc info_date;
run;
Output:
status_ total_ total_ net_ balance_
Obs id_acc acc_no code info_date act_bal debt credit charges at_def delinquey
1 1111 999585548 D1A 30JUN2007 500 1000 2000 -1000 500 1
2 1111 999585548 AAA 31JUL2007 500 1000 2000 -1000 . .
3 1111 999585544 D1A 31AUG2007 400 5000 4600 400 400 3
4 1111 999585547 D2A 30SEP2007 500 1000 2000 -1000 500 4
5 1111 999585540 D1D 31OCT2007 300 1000 2000 -1000 300 5
6 1111 999585544 AAA 30NOV2007 500 1300 2000 -700 . .
7 1111 999585547 AAA 31DEC2007 500 1000 2000 -1000 . .
8 1111 999585540 D1D 31JAN2008 500 1000 2000 -1000 500 8
9 1111 999585544 D2D 28FEB2008 200 1800 2000 -200 200 9
10 1111 999585547 D3D 31MAR2008 500 1000 2000 -1000 500 10
11 1111 999585540 D4D 30APR2008 500 1000 2000 -1000 500 11
12 1111 999585540 D5D 31MAY2008 500 1000 2000 -1000 500 12
13 1111 999585540 AAA 30JUN2008 500 1000 2000 -1000 . .
14 1111 999585540 AAA 31JUL2008 100 2000 3000 -1000 . .
15 1111 999595540 D1D 31AUG2008 200 1000 2000 -1000 200 15
16 1111 999587540 LEP 30SEP2008 500 1000 2000 -1000 . .
17 1111 999587540 LEP 31OCT2008 500 1000 2000 -1000 . .
18 1111 999585540 LEP 30NOV2008 500 1000 2000 -1000 . .
19 1112 999505548 D2A 30JUN2007 500 1000 2000 -1000 500 1
20 1112 999505548 AAA 31JUL2007 400 1000 2000 -1000 . .
21 1112 999505544 D1A 31AUG2007 500 1000 2000 -1000 500 21
22 1112 999505547 D2A 30SEP2007 500 1000 2000 -1000 500 22
23 1112 999505540 D1D 31OCT2007 500 1000 2000 -1000 500 23
24 1112 999505544 D2D 30NOV2007 500 1000 2000 -1000 500 24
25 1112 999505547 D3D 31DEC2007 700 1000 2000 -1000 700 25
26 1112 999505540 D4D 31JAN2008 500 1000 2000 -1000 500 26
27 1112 999505544 D5D 28FEB2008 500 1000 2000 -1000 500 27
28 1112 999505547 D6D 31MAR2008 500 1000 2000 -1000 500 28
29 1112 999505540 LEP 30APR2008 500 1000 2000 -1000 . .
30 1112 999505540 LEP 31MAY2008 800 1000 2000 -1000 . .
31 1112 999505540 LEP 30JUN2008 500 1000 2000 -1000 . .
32 1112 999505540 LEP 31JUL2008 500 1000 2000 -1000 . .
33 1112 999505540 LEP 31AUG2008 500 1000 2000 -1000 . .
34 1112 999505540 LEP 31OCT2008 900 1000 2000 -1000 . .
35 1112 999505540 LEP 30NOV2008 500 1000 2000 -1000 . .
what i want to create one more variable net_sum:
1-sum of net_charges of delinquency sum(3,4,5) and subtract the first default_balance of that group from total sum.
subsequently the sum of sum(8,9,10,11,12) and subtract the first default_balance of that group from total sum.
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.