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;
... View more
