How to calculate previous products holding using PROC SQL?

[Kanyange]

Dear All,

I have the following tables

- Campaign table : contains the date the campaign was sent

- Transaction table : contains the purchase date and the name of the product

- The desired outcome, is to have the number products the customer hold at the time of each campaign date

I want to keep all the campaign tables rows as they are and just add a column called no of products they have at that date

Is there anyway of doing this using Proc sql?

Thank you so much for your help

Campaign table
Id	Campaign_date
1	07/11/2017 00:00
1	14/11/2017 00:00
1	19/11/2017 00:00
1	23/11/2017 00:00
1	24/11/2017 00:00

Transactions table
Id	Purchase date	Product
1	08/06/2012 00:00	A
1	29/03/2014 00:00	B
1	29/03/2014 00:00	C
1	08/11/2017 00:00	D

Desired Output
Id	Campaign_date	No_products
1	07/11/2017 00:00	3
1	14/11/2017 00:00	4
1	19/11/2017 00:00	4
1	23/11/2017 00:00	4
1	24/11/2017 00:00	4

[PeterClemmensen]

So the initial number of products for Customer ID 1 os 3? How do we know that?

[Kanyange]

Hi,

The initial number is 3 because if you look at the purchase date, the customer has bought 3 different products, before 07/11/2018...

So on 7/11/2017, he had 3 products then he bought an extra one 0n 08/11/2017...so the next dates should have 4 next to them...

Not sure if it makes sense...?

Thank you

[andreas_lds]

Can you post the test data as datasteps, so that we have exactly the data-types and variable names you use?

The logic to be used is: select count(*) from transaction having purchase_date < campaign_date. Right?

[Kanyange]

I found a solution 🙂 and it works perfectly...

proc sql;
create table previous_products as
select
  prod.email
, prod.campaign_date
, count(distinct case when product is not null and prod_before_campaign=1 then product end) as No_products

from
(select distinct a.*
      ,b.purchase_date
      ,b.product
      ,case when purchase_date < campaign_date then 1 else 0 end as prod_before_campaign
from (select email, mailing_id, campaign_date from analysis.campaign_dataset where email='xxxxxx') a
left join (select distinct email, purchase_date, product from analysis.transaction_data
where email='xxxx') b
on a.email=lower(b.email) order by campaign_date, purchase_date) prod
group by 1,2;
quit;