topic Finding if customer has already products in last 2 months in New SAS User

Finding if customer has already products in last 2 months

msf2021 — Thu, 02 Sep 2021 12:37:07 GMT

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

Re: Finding if customer has already products in last 2 months

Ksharp — Thu, 02 Sep 2021 13:34:59 GMT

data have;
input Date_begin_policy : ddmmyy12.  Customer_id  $ Policy_id;
format Date_begin_policy ddmmyy10.;
cards;
01-09-2019             A            111
02-10-2019             A            123
09-07-2019             A            126
;
run;

proc sql;
create table want as
select *,
exists(select * from have where Customer_id=a.Customer_id  and 
Date_begin_policy between intnx('month',a.Date_begin_policy,-1) and a.Date_begin_policy-1) as has_policy_month_before,
exists(select * from have where Customer_id=a.Customer_id  and 
Date_begin_policy between intnx('month',a.Date_begin_policy,-2) and intnx('month',a.Date_begin_policy,-1)-1) as has_policy_month_less2

 from have as a;
quit;

Re: Finding if customer has already products in last 2 months

msf2021 — Thu, 02 Sep 2021 14:01:52 GMT

Thank you!! What if i want also less 3 months?

Re: Finding if customer has already products in last 2 months

Ksharp — Fri, 03 Sep 2021 11:44:22 GMT

OK. Here is .

data have;
input Date_begin_policy : ddmmyy12.  Customer_id  $ Policy_id;
format Date_begin_policy ddmmyy10.;
cards;
01-09-2019             A            111
02-10-2019             A            123
09-07-2019             A            126
;
run;

proc sql;
create table want as
select *,
exists(select * from have where Customer_id=a.Customer_id  and 
Date_begin_policy between intnx('month',a.Date_begin_policy,-1) and a.Date_begin_policy-1) as has_policy_month_before,
exists(select * from have where Customer_id=a.Customer_id  and 
Date_begin_policy between intnx('month',a.Date_begin_policy,-2) and intnx('month',a.Date_begin_policy,-1)-1) as has_policy_month_less2,
exists(select * from have where Customer_id=a.Customer_id  and 
Date_begin_policy between intnx('month',a.Date_begin_policy,-3) and intnx('month',a.Date_begin_policy,-2)-1) as has_policy_month_less3


 from have as a;
quit;