Hi everyone, hope to find you well!
I have a table called "policies" that has the following data:
Date_begin_policy Customer_id Policy_id
01-09-2019 A 111
02-10-2019 A 123
09-07-2019 A 126
and i want to build 2 columns "has_policy_month_before" and "has_policy_month_less2" that for each date_begin_policy checks if customer has already other policies in date_begin_policy -1month and date_begin_policy - 2 month:
Date_begin_policy Customer_id Policy_id has_policy_month_before has_policy_month_less2
01-09-2019 A 111 no yes
02-10-2019 A 123 yes no
09-07-2019 A 126 no no
I would do it like this in Teradata sql:
select t1.customer_id
, t1.policy_id
, t1.date_begin_policy
, case
when trunc(t2.date_begin_policy, 'mm') >= add_months(trunc(t1.date_begin_policy, 'mm'), -1)
then 'yes'
else 'no'
end as has_policy_month_before
, case
when trunc(t2.date_begin_policy, 'mm') < add_months(trunc(t1.date_begin_policy, 'mm'), -1)
then 'yes'
else 'no'
end as has_policy_month_less2
from mvt_data as t1
left join mvt_data as t2 on t2.customer_id = t1.customer_id
and trunc(t2.date_begin_policy, 'mm') >= add_months(trunc(t1.date_begin_policy, 'mm'), -2)
and trunc(t2.date_begin_policy, 'mm') < trunc(t1.date_begin_policy, 'mm')
where t1.customer_id = 'A'
order by t1.policy_id asc;
but the problem is the add_months function and the trunc function are not like this in SAS. Could anyone please help me?
Please note i really would like to do it in sql and not sas syntax preferably.
Thanks