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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.