How to get a tenure of a client with different start end dates?

bw92 — Fri, 02 Nov 2018 19:32:45 GMT

Hey Guys!

Just wondering how to calculate the time a client spends. As you can see, there were some gaps that this client was not holding any policy. I would like to take those gap time out of my equation. I am using

proc sql;
create table client_lost as 
select distinct party_id,
max(end_date) as max_end_date format date9.,
min(START_DATE) as min_start_date format date9.
group by 1
;quit;

data client_lost_2;
set client_lost;
tenure = yrdif(min_start_date,max_end_date,'Actual'); 
run;

Thank you in advance

Re: How to get a tenure of a client with different start end dates?

PGStats — Sat, 03 Nov 2018 04:23:55 GMT

Count the days with an array:

data have;
input client_id policy_id (start_date end_date) (:mmddyy10.);
datalines;
1 101 1/1/1990 8/1/1992
1 102 5/1/1991 11/1/1991
1 103 5/1/1993 8/1/2000
1 104 1/1/2005 8/1/2010
;

proc sql;
select min(start_date), max(end_date)
into :fromdt trimmed, :todt trimmed
from have; 
quit;

data want;
array days {&fromdt:&todt};
do until(last.client_id);
    set have; by client_id;
    do dt = start_date to end_date;
        days{dt} = 1;
        end;
    firstDt = min(firstDt, start_date);
    lastDt = max(lastDt, end_date);
    end;
totalPolicyDays = sum(of days{*});
totalPolicySpan = intck("day", firstDt, lastDt);
keep client_id totalPolicyDays totalPolicySpan;
run;

proc print; run;

                                           total     total
                                client_    Policy    Policy
                         Obs       id       Days      Span

                          1        1        5633      7517

topic How to get a tenure of a client with different start end dates? in New SAS User

How to get a tenure of a client with different start end dates?

Re: How to get a tenure of a client with different start end dates?