07-21-2012 12:51 PM
I have been working on this query for 2 days and it has come to a point where I am stuck and don't know how to proceed. I was using PROC SQL
1| Data Information 1 large database table with the following columns:
Timekey - numeric 2 digit that represents month-ends from 2008 to 2012 (ex 001 is Jan'08 002 is Feb'08 etc..)
Customer_number- numeric 10 digits
Customer_number- A- active I- cancelled U-unknown
Product_type- numeric 3 digit that lists the type of product
For each timekey there are around 200,000 records (customers) for all products
I would like to compare customers for each consecutive month and would like to COUNT all customers that changed status from Active to Inactive and also COUNT all customers that dropped off the database from one month to the next
So the query will start with comparing all customers in timekey 001 (Jan'08) to 002 (Feb'08) and count all the customers that were active in Jan'08 that became inactive in Feb'08 and also COUNT all customers that existed in Jan'08 but now do not not exist in Feb'08. Then the query will do the same counting for timekeys 002 (Feb'08) to 003 (Mar'08).. followed by timekeys 003 to 004 etc..
The counts should be grouped by timekey and product type.
Support would be much appreciated !
07-21-2012 01:44 PM
Something like this ?
format timekey z3.;
length status $1;
input timekey customer status $ productType;
001 123 A 1
001 123 U 2
001 234 A 1
002 123 A 2
002 234 I 1
create table cancelledCustomers as
a.timekey + 1 as timekey format=z3.,
sum(b.customer is missing) as nbDroppedOut,
sum(a.status="A" and b.status="I") as nbBecameInactive
from have as a left join have as b
on a.timeKey + 1 = b.timekey and
a.productType = b.productType and
a.customer = b.customer
where a.timekey < (select max(timekey) from have)
group by a.timekey, a.productType;