sql - last transaction date

Accepted Solution Solved
Reply
Trusted Advisor
Posts: 1,204
Accepted Solution

sql - last transaction date

Hi Experts,

I've two tables

1.     campaign_detail (campaign transactions for customers for a specified time period)

2.     sale_detail (Contains historical info for customers)

I am trying to get last transaction date for campain customers (campaign_detail table) from sale_detail table for a number of cust_id. Some of the customers have multple transactions (please see table below cust_id=20000915003) . Query given below picking last transaction date (last_tran_date)  based on most recent tran_date from campaign_detail table (tran_date=2014-01-26) and tagging it with all of the transaction done by cust_id=20000915003. I want to tag last_tran_date for each of tran_date not based on maximum transaction date of most recent purchase. Please have a look on the following code and advise changes for the desired solution?

SELECT b.cust_id,max(b.tran_date) as last_tran_date

FROM sale_detail b inner join campaign_detail a

on b. cust_id = a. cust_id
and b.tran_date < a.tran_date
group by b.site_id

cust_id                 tran_date         last_tran_date
20000915003      2013-06-07      2013-12-07
20000915003      2013-10-06      2013-12-07
20000915003      2014-01-26      2013-12-07

Thanks in advance for your help.

Regards,

Naeem


Accepted Solutions
Solution
‎03-05-2014 03:06 PM
PROC Star
Posts: 7,363

Re: sql - last transaction date

Your original code included a site_id variable, which is why I added it.  Just remove it from the proc sql.  i.e.,:

proc sql;

  create table need as

    SELECT a.cust_id,

           a.tran_date,

           b.tran_date as last_tran_date

      FROM campaign_detail a

        join sale_detail b

        on a.cust_id = b.cust_id and

           last_tran_date < a.tran_date

  ;

  create table want as

    select distinct *

      from need

        group by cust_id,tran_date

          having last_tran_date eq max(last_tran_date)

   ;

quit;

View solution in original post


All Replies
Super Contributor
Posts: 578

Re: sql - last transaction date

I got lost on the requirements.  Perhaps if you would show some examples of both tables and how they relate, we might could help.

Trusted Advisor
Posts: 1,204

Re: sql - last transaction date

Hi DBailey,

Please see below example for your review.

campaign_detail table

cust_id                 tran_date   

20000915003      2013-06-07

20000915003      2013-10-06

20000915003      2014-01-26


In campaign_detail table I took only one customer (cust_id=20000915003) which has made 3 transactions during the campaign. Now I want to join these 3 transaction with  sale_detail table (given below) that contains historical info for customer (cust_id=20000915003) and want to get previous transaction date. As 2014-01-26 is the latest transaction date from campaign_detail table so query is joining most recent previous transaction date (max(b.tran_date) with each of the 3 transactions from sale_detail table. Whereas it should be prior date based on transaction date of each row.  Please see desired output at the bottom.     

SELECT b.cust_id,max(b.tran_date) as last_tran_date

FROM sale_detail b inner join campaign_detail a

on b. cust_id = a. cust_id
and b.tran_date < a.tran_date
group by b.site_id

sale_detail table.

cust_id                 trans_date      sales        

20000915003      2013-12-13      55.92

20000915003      2012-04-13      101.83

20000915003      2012-09-10      -99.97

20000915003      2012-11-09      320.45

20000915003      2013-05-28      110.91

20000915003      2013-11-09      49.48

20000915003      2012-02-27      -161.92

20000915003      2012-03-05      -22.09

20000915003      2012-05-03      114.29

20000915003      2013-08-06      16.54

20000915003      2014-01-04      39.99

20000915003      2014-02-08      32.40

20000915003      2013-01-02      116.25

20000915003      2013-06-18      82.90

20000915003      2013-06-25      34.95

20000915003      2012-02-23      59.32

20000915003      2013-04-01      66.90

20000915003      2013-07-15      316.77

20000915003      2014-02-04      42.89

20000915003      2013-10-10      38.84

20000915003      2013-02-14      367.21

20000915003      2012-04-18      114.29

20000915003      2013-04-02      43.93

20000915003      2013-02-22      123.64

20000915003      2014-01-30      23.70

20000915003      2013-10-25      91.95

20000915003      2013-11-21      30.60

20000915003      2013-12-24      180.50

20000915003      2013-12-29     -160.66

20000915003      2012-06-08      271.77

20000915003      2013-02-05      44.93

20000915003      2013-04-30      189.09

20000915003      2013-09-24      142.87

20000915003      2013-10-06      42.95

20000915003      2014-02-24      -42.89

20000915003      2012-02-21      86.79

20000915003      2012-07-25      93.84

20000915003      2013-12-02      43.82

20000915003      2014-02-13      224.41

20000915003      2012-06-19      93.97

20000915003      2012-09-17      84.20

20000915003      2012-09-19      14.75

20000915003      2013-01-15      99.64

20000915003      2013-05-09      127.71

20000915003      2013-07-09      263.30

20000915003      2013-07-17      54.96

20000915003      2013-12-07      30.60

20000915003      2012-07-05      173.53

20000915003      2013-06-07      41.62

20000915003      2013-11-19      88.79

20000915003      2012-03-19      77.06

20000915003      2012-07-23      99.97

20000915003      2013-08-01      82.40

20000915003      2013-10-09      189.71

20000915003      2014-01-26      36.00

20000915003      2013-01-21      98.49

20000915003      2013-10-01      397.00

20000915003      2012-03-08      187.81

20000915003      2012-04-17      85.39

20000915003      2012-05-22      134.29

20000915003      2012-08-30      90.82

20000915003      2012-09-15      81.83

20000915003      2012-10-09      149.61

20000915003      2013-02-06      79.94

20000915003      2012-11-24      135.35

20000915003      2013-08-29      44.93

20000915003      2012-08-22      156.34

20000915003      2012-02-16      196.55

20000915003      2012-09-21      451.36

20000915003      2012-10-06      73.62

20000915003      2013-04-16      146.78

20000915003      2013-07-16      173.38

20000915003      2013-08-13      45.97

20000915003      2014-01-13      14.60

20000915003      2012-04-27      235.04

20000915003      2013-07-31      34.76

20000915003      2013-07-30      46.95

20000915003      2013-08-08      -70.49

20000915003      2013-09-19      36.00

Desired output

cust_id                 tran_date         last_tran_date

20000915003      2013-06-07      2013-05-28

20000915003      2013-10-06      2013-10-01

20000915003      2014-01-26      2014-01-13

PROC Star
Posts: 7,363

Re: sql - last transaction date

This could definitely be reduced to one step, but I think that the following does what you want:

proc sql;

  create table need as

    SELECT a.site_id,

           a.cust_id,

           a.tran_date,

           b.tran_date as last_tran_date

      FROM campaign_detail a

        join sale_detail b

        on a.cust_id = b.cust_id and

           a.site_id = b.site_id and

           last_tran_date < a.tran_date

  ;

  create table want as

    select distinct *

      from need

        group by site_id,cust_id,tran_date

          having last_tran_date eq max(last_tran_date)

   ;

quit;

Trusted Advisor
Posts: 1,204

Re: sql - last transaction date

Hi Arthur,

Thanks for suggesting solution. I tried this but not giving me desired results. Is this possible to take two tables campaign_detail table and sale_detail table and write code discarding my query to generate desired results.

Thanks once again for giving consideraton to my question.

Regards,

Naeem

PROC Star
Posts: 7,363

Re: sql - last transaction date

Given your example data, the code I suggested produces a table called 'want' that contains:

site_id    cust_id       tran_date      last_tran_date

1 20000915003 07JUN2013 28MAY2013

1 20000915003 06OCT2013 01OCT2013

1 20000915003 26JAN2014 13JAN2014

which is what you indicated you wanted.  In short, I don't understand what you are now asking.

Trusted Advisor
Posts: 1,204

Re: sql - last transaction date

Hi Arthur,

Yes, this is working for a cust_id given in example but I've thousands of cust_ids and there is only cust_id variable no site_id variable in my dataset.

My apology for not mentioning it earlier.

Regards,

Naeem

Solution
‎03-05-2014 03:06 PM
PROC Star
Posts: 7,363

Re: sql - last transaction date

Your original code included a site_id variable, which is why I added it.  Just remove it from the proc sql.  i.e.,:

proc sql;

  create table need as

    SELECT a.cust_id,

           a.tran_date,

           b.tran_date as last_tran_date

      FROM campaign_detail a

        join sale_detail b

        on a.cust_id = b.cust_id and

           last_tran_date < a.tran_date

  ;

  create table want as

    select distinct *

      from need

        group by cust_id,tran_date

          having last_tran_date eq max(last_tran_date)

   ;

quit;

Trusted Advisor
Posts: 1,204

Re: sql - last transaction date

Thanks so much Arthur. Syntax provided is working and I am able to perform my analysis. I don't know how to mark it correct because option to mark is not available. Sometimes it works sometimes not I am not how to flag it?


Regards,


Naeem

PROC Star
Posts: 7,363

Re: sql - last transaction date

You probably have to make sure that you are logged on with your stat@sas user name.

Trusted Advisor
Posts: 1,204

Re: sql - last transaction date

I did that but still options are not available.

Regards,

Naeem

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 10 replies
  • 2004 views
  • 1 like
  • 3 in conversation