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
> 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.
@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.
> I have updated the example
Good. Now try the proposed solutions.
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.
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.
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.