|
Hi There,
I need to get data from a very old database which can only store debts up to
$99,999.99 per case on the db. Each case has a unique number and each client
has a unique id.
If a client has a debt over the above amount then the user sets up a new
case and clears the balance under the new case. If the amount is over
$300,000 they would set up a third case and so on. I need to know the amount
of debt per investigation and for these to be only counted once.
I've made the decision that any 2 or more cases, with a debt established,
cleared within 21 days are from the same investigation. Not all cases have a
debt over the $99,000. My code is as follows and seems to work but I know
there must be a more efficient way of coding it:
Data is sorted by client_id datecleared ;
data trace2;
set trace ;
by client_id datecleared;
if first.client_id then set_num = 1 ;
if client_id = lag( client_id )
and datecleared - lag( datecleared ) > 21
then set_num + 1 ;
run;
proc summary data = trace2 nway missing;
class client_id set_num ;
var debt_established ;
output out = trace3 sum = tot_debt_established ;
run;
data added ;
merge trace2 trace3 ;
by client_id set_num ;
if first.set_num then counter = 1 ;
if client_id = lag(client_id)
and set_num = lag(set_num)
and tot_debt_established = lag(tot_debt_established)
then counter+1 ;
run;
data case1 case2 case3 case4 case5 case6 case7 case8 case9 case10 ;
set added ;
if counter = 1 then output case1 ;
if counter = 2 then output case2 ;
if counter = 3 then output case3 ;
if counter = 4 then output case4 ;
if counter = 5 then output case5 ;
if counter = 6 then output case6 ;
if counter = 7 then output case7 ;
if counter = 8 then output case8 ;
if counter = 9 then output case9 ;
if counter = 10 then output case10 ;
run ;
data added_up ;
merge case1
(in = __1__)
case2
(in = __2__
keep = client_id debt_established db_unique_id
rename = (debt_established = debt_established2
db_unique_id = db_unique_id2))
case3
(in = __3__
keep = client_id debt_established db_unique_id
rename = ( debt_established = debt_established3
db_unique_id = db_unique_id3))
case4
(in = __4__
keep = client_id debt_established db_unique_id
rename = ( debt_established = debt_established4
db_unique_id = db_unique_id4))
case5
(in = __5__
keep = client_id debt_established db_unique_id
rename = ( debt_established = debt_established5
db_unique_id = db_unique_id5))
case6
(in = __6__
keep = client_id debt_established db_unique_id
rename = ( debt_established = debt_established6
db_unique_id = db_unique_id6))
case7
(in = __7__
keep = client_id debt_established db_unique_id
rename = ( debt_established = debt_established7
db_unique_id = db_unique_id7))
case8
(in = __8__
keep = client_id debt_established db_unique_id
rename = ( debt_established = debt_established8
db_unique_id = db_unique_id8))
case9
(in = __9__
keep = client_id debt_established db_unique_id
rename = ( debt_established = debt_established9
db_unique_id = db_unique_id9))
case10
(in = __10__
keep = client_id debt_established db_unique_id
rename = (debt_established = debt_established10
db_unique_id = db_unique_id10)) ;
by client_id ;
if __1__ ;
run ;
Can anyone please help?
I know the above aint pretty!
regards
Steve
| 
