Stuck with Complex Query- Counting Data for Cancelled Customers

MaxBavarian — Sat, 21 Jul 2012 16:51:36 GMT

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

Query

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 !

Max

Re: Stuck with Complex Query- Counting Data for Cancelled Customers

PGStats — Sat, 21 Jul 2012 17:44:00 GMT

Something like this ?

data have;
format timekey z3.;
length status $1;
input timekey customer status $ productType;
datalines;
001 123 A 1
001 123 U 2
001 234 A 1
002 123 A 2
002 234 I 1
;

proc sql;
create table cancelledCustomers as
select
      a.timekey + 1 as timekey format=z3.,
     a.productType,
     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;

quit;

topic Re: Stuck with Complex Query- Counting Data for Cancelled Customers in SAS Procedures

Stuck with Complex Query- Counting Data for Cancelled Customers

Re: Stuck with Complex Query- Counting Data for Cancelled Customers