BookmarkSubscribeRSS Feed
omerzeybek
Obsidian | Level 7

    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

5 REPLIES 5
Haikuo
Onyx | Level 15

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
Obsidian | Level 7

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
Lapis Lazuli | Level 10

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
Onyx | Level 15

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
Pyrite | Level 9

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 sqlcreate 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;



sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

SAS Enterprise Guide vs. SAS Studio

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 5 replies
  • 1002 views
  • 6 likes
  • 4 in conversation