topic Re: taking counts for a specific time interval in SAS Enterprise Guide

taking counts for a specific time interval

omerzeybek — Wed, 10 Dec 2014 18:57:49 GMT

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

Re: taking counts for a specific time interval

Haikuo — Wed, 10 Dec 2014 19:01:24 GMT

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

Re: taking counts for a specific time interval

omerzeybek — Wed, 10 Dec 2014 19:34:00 GMT

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

Re: taking counts for a specific time interval

Haikuo — Wed, 10 Dec 2014 19:41:33 GMT

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

Re: taking counts for a specific time interval

DBailey — Wed, 10 Dec 2014 19:50:46 GMT

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;

Re: taking counts for a specific time interval

agoldma — Sat, 13 Dec 2014 05:45:34 GMT

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;