Hi
I have transcation data which records every transaction in my department store
i know transaction time
and also product types are listed in my transactional data.
I want to create a column that is showing
that for each transaction what is the number of distinct number of product types he purchased referring t-365 and t -1 interval from the purchase date
i couldnt find a conditional counting way to take counts for a specific interval...
Thank You very much
I am aware that you have explained your question very well, but you can do it better by providing sample data 'Have' and 'Want', please.
Haikuo
i have date of purchase / product type / customer number / Transaction Ref / Purchase date
I want crate a coloumn that records count of different product types that a customer purchased one year prior to referenced transaction
thank you very much
Haven't tested...but here's the normal sql approach.
proc sql;
create table want as
select
t1.customer_id,
t1.purchase_date,
count(distinct t2.product_type) as Products_In_Last_Year
from
have t1
left outer join have t2
on t1.customer_id=t2.customer_id
and t2.purchase_date >= t1.purchase_date - 365
and t2.purchase_date < t1.purchase_date
group by
t1.customer_id,
t1.purchase_date;
quit;
Not the most efficient way to run, but the easiest way to code:
proc sql;
create table want as
select *, (select count(distinct product_type) from have where customer_number=a.customer_number and a.purchase_date -365 <= purchase_date <= a.purchase_date-1) as type_ct
from have a
;
quit;
Good luck,
Haikuo
Hai.kuo definitely lived up to his name here, but... this question is not marked as "answered" yet, so I thought I'd provide one more option (similar to DBailey)
Assuming that Product_type is a character variable
data have;
infile datalines ;
informat Purchase_Date date9.;
format Purchase_Date date9.;
length Transaction_Ref $5 Product_Type $10;
input customer_number Purchase_Date Transaction_Ref Product_Type ;
datalines;
1 16JUL2014 12345 TYPE1
1 17JUL2014 12346 TYPE1
1 18JUL2014 12347 TYPE1
1 18JUL2014 12347 TYPE2
1 18JUL2014 12347 TYPE3
1 19JUL2014 12347 TYPE1
2 16JUL2014 22345 TYPE1
2 17JUL2014 22346 TYPE1
2 18JUL2014 22347 TYPE1
2 18JUL2014 22347 TYPE2
2 18JUL2014 22347 TYPE3
2 19JUL2014 22347 TYPE1
;
run;
proc sql; create table want as
select a.customer_number
,a.Purchase_Date
,a.Transaction_Ref
,a.Product_Type
,count( DISTINCT
(case when b.purchase_date between a.purchase_date-365
and a.purchase_date-1
then b.product_type
else ''
end)
) as type_ct
from have A
left join have B on a.customer_number = b.customer_number
group by 1,2,3,4
;quit;
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.
Find more tutorials on the SAS Users YouTube channel.