taking counts for a specific time interval

[omerzeybek]

    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

[Haikuo]

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

[omerzeybek]

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

[DBailey]

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;

[Haikuo]

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

[agoldma]

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;