BookmarkSubscribeRSS Feed
Kanyange
Fluorite | Level 6

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 
IdCampaign_date
107/11/2017 00:00
114/11/2017 00:00
119/11/2017 00:00
123/11/2017 00:00
124/11/2017 00:00

 

Transactions table  
IdPurchase dateProduct
108/06/2012 00:00A
129/03/2014 00:00B
129/03/2014 00:00C
108/11/2017 00:00D

 

Desired Output  
IdCampaign_dateNo_products
107/11/2017 00:003
114/11/2017 00:004
119/11/2017 00:004
123/11/2017 00:004
124/11/2017 00:004
4 REPLIES 4
PeterClemmensen
Tourmaline | Level 20

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

Kanyange
Fluorite | Level 6

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
Jade | Level 19

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
Fluorite | Level 6

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;

hackathon24-white-horiz.png

2025 SAS Hackathon: There is still time!

Good news: We've extended SAS Hackathon registration until Sept. 12, so you still have time to be part of our biggest event yet – our five-year anniversary!

Register Now

How to Concatenate Values

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 4 replies
  • 1324 views
  • 0 likes
  • 3 in conversation