CODEWITHSUNDEEP
sqlsas
vknowles
vknowles

Reputation: 784

Ideas to improve SAS SQL query

This is based on a question I answered here about how to use SAS to solve the following problem. [NOTE: There is a working SAS data step solution, but I was just trying to see how it would work in SQL.]

The problem (a little hard to describe) is to look at each transaction for a customer, look back 90 days from that transaction's date, count the total number of the customer's transactions in that 90-day window, save that number with the original transaction, count the number of distinct account managers handling the customer's transactions in that 90-day window, and save that number with the original transaction.

Here is the data step to initialize a test transaction dataset and my SAS SQL solution:

data transaction;
    length customerid $ 12 accountmanager $7 transactionid $ 12;
    input CustomerID AccountManager TransactionID Transaction_Time datetime.;
    format transaction_time datetime.;
    datalines;
1111111111  FA001          TR2016001      08SEP16:11:19:25
1111111111  FA001          TR2016002      26OCT16:08:22:49
1111111111  FA002          TR2016003      04NOV16:08:05:36
1111111111  FA003          TR2016004      04NOV16:17:15:52
1111111111  FA004          TR2016005      25NOV16:13:04:16
1231231234  FA005          TR2016006      25AUG15:08:03:29
1231231234  FA005          TR2016007      16SEP15:08:24:24
1231231234  FA008          TR2016008      18SEP15:14:42:29
;;;;
run;

proc sql;
    create table want as
        select mgrs.*, trans.tranct
        from
            (select t3.customerid, t3.accountmanager, t3.transactionid, t3.transaction_time, count(*) as mgrct
                from (select distinct t1.*, t2.accountmanager as m2
                      from transaction t1, transaction t2
                      where t1.customerid=t2.customerid
                            and
                          datepart(t1.transaction_time) >= datepart(t2.transaction_time)-90
                            and
                          t2.transaction_time <= t1.transaction_time) t3
                group by t3.customerid, t3.accountmanager, t3.transactionid, t3.transaction_time) mgrs,
            (select t4.customerid, t4.transactionid, count(*) as tranct
                from transaction t4, transaction t5
                where t4.customerid=t5.customerid
                        and
                      datepart(t4.transaction_time) >= datepart(t5.transaction_time)-90
                        and
                      t5.transaction_time <= t4.transaction_time
                group by t4.customerid, t4.transactionid) trans
        where mgrs.customerid=trans.customerid and mgrs.transactionid=trans.transactionid;
quit;

The result looks like this:

customerid  accountmanager transactionid  Transaction_Time mgrct tranct
1111111111  FA001          TR2016001      08SEP16:11:19:25   1     1 
1111111111  FA001          TR2016002      26OCT16:08:22:49   1     2
1111111111  FA002          TR2016003      04NOV16:08:05:36   2     3
1111111111  FA003          TR2016004      04NOV16:17:15:52   3     4
1111111111  FA004          TR2016005      25NOV16:13:04:16   4     5
1231231234  FA005          TR2016006      25AUG15:08:03:29   1     1
1231231234  FA005          TR2016007      16SEP15:08:24:24   1     2
1231231234  FA008          TR2016008      18SEP15:14:42:29   2     3

I haven't used SQL for a long time, so I would like to know if there is a more elegant SQL solution. I thought it would be simpler, but actually the SAS data step code seems simpler than this SQL query.

Upvotes: 1

Views: 95

Answers (1)

Tom
Tom

Reputation: 51621

I think you just need to join the table with itself.

proc sql noprint ;
 create table want as
   select a.*
        , count(distinct b.accountmanager) as mgrct
        , count(*) as tranct
   from transaction a
   left join transaction b
   on a.customerid = b.customerid
    and b.transaction_time <= a.transaction_time
    and datepart(a.transaction_time)-datepart(b.transaction_time)
        between 0 and 90
   group by 1,2,3,4
 ;
quit;

Results

1111111111 FA001 TR2016001 08SEP16:11:19:25 1 1  
1111111111 FA001 TR2016002 26OCT16:08:22:49 1 2  
1111111111 FA002 TR2016003 04NOV16:08:05:36 2 3  
1111111111 FA003 TR2016004 04NOV16:17:15:52 3 4  
1111111111 FA004 TR2016005 25NOV16:13:04:16 4 5  
1231231234 FA005 TR2016006 25AUG15:08:03:29 1 1  
1231231234 FA005 TR2016007 16SEP15:08:24:24 1 2  
1231231234 FA008 TR2016008 18SEP15:14:42:29 2 3

Upvotes: 3

Related Questions