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 |
So the initial number of products for Customer ID 1 os 3? How do we know that?
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
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?
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;
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!
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.
Ready to level-up your skills? Choose your own adventure.