topic Re: Find maximum difference between date column and current date in years. in SAS Programming

Find maximum difference between date column and current date in years.

Saurabh_Rana — Tue, 20 Oct 2020 05:36:54 GMT

I have customer policy data where customer can have multiple policies against his/her name along with the policy start date. I want to derive the time in years since the customer is associated with us so basically I want to find time between the policy start date of the first policy and the current date.

For example->

Input

Customer_Name Policy_Start_Date
cust_1        20/10/2019
cust_1        20/02/2020 
cust_2        20/10/2018
cust_2        26/10/2019 

Output 

Customer_Name Customer_Since
cust_1        1 Year
cust_2        2 Year

Re: Find maximum difference between date column and current date in years.

ChrisNZ — Tue, 20 Oct 2020 05:30:20 GMT

> customer can have multiple policies against his/her name along with the policy start date

Isn't that the same as finding the minimum (oldest) date for each customer?

Look at the different options of the INTCK function to derive the number of years the way you want.

Re: Find maximum difference between date column and current date in years.

andreas_lds — Tue, 20 Oct 2020 05:31:53 GMT

@Saurabh_Rana wrote:

I have customer policy data where customer can have multiple policies against his/her name along with the policy start date. I want to derive the time in years since the customer is associated with us so basically I want to find time between the policy start date of the first policy and the current date.

For example->

Input
Customer_Name Policy_Start_Date
cust_1        20/10/2019
cust_2        20/10/2018

Output 

Customer_Name Customer_Since
cust_1        1 Year
cust_2        2 Year

What have you tried so far? You are talking about having multiple policies per customer, but in your example data there is only one observation for each customer. Please take care that the data you post matches what you have as close as possible. Also post the data in usable form.

Re: Find maximum difference between date column and current date in years.

Saurabh_Rana — Tue, 20 Oct 2020 05:36:08 GMT

I have updated the example

Re: Find maximum difference between date column and current date in years.

ChrisNZ — Tue, 20 Oct 2020 06:00:55 GMT

> I have updated the example

Good. Now try the proposed solutions.

Re: Find maximum difference between date column and current date in years.

andreas_lds — Tue, 20 Oct 2020 06:01:13 GMT

Untested, the data is still not in usable form.

proc sort data=have out=sorted;
  by customer_name policy_start_date;
run;

data want;
  set sorted;
  by customer_name;

  if first.customer_name;
  years = yrdif(policy_start_date, today());
run;

You may want to look at the documentation of yrdif, the function has a third parameter that could be used to tweak the result.

Re: Find maximum difference between date column and current date in years.

Kurt_Bremser — Tue, 20 Oct 2020 09:39:50 GMT

SQL:

proc sql;
create table want as
  select
    customer_name,
    intck('year',min(policy_start_date),today(),'c') as years
  from have
  group by customer_name
;
quit;

Modify the last parameter of INTCK as needed.