Averaging amount spent per transaction over a month up to this transaction

Reply
New Contributor
Posts: 4

Averaging amount spent per transaction over a month up to this transaction

Hello everyone,

I saw a paper writing about how to build credit dataset and am curious how to perform the above aggregation in sas with maybe proc sql query?


for example, I have a transaction happen on jun 24 2014, I want to find total amount spent with the account for the past 30 days prior to this transaction (may 24-jun 24) and divided it by the # of transactions in this time frame. I need to do this for each transaction.

the data looks like alltran:

    obs  tran_date  tran_amt  mechr_cate_cd
    1    05/04/14   5.32      4633
    2    05/06/14   8.97      5846
    3    06/02/14   10.13     7996

I want the output to look like

    obs  tran_date  tran_amt  mechr_cate_cd  avg_amt30
    1    05/04/14   5.32      4633           5.32
    2    05/06/14   8.97      5846           7.15
    3    06/02/14   10.13     7996           8.14

I am thinking of using correlated subquery in proc sql:

    proc sql;
    create table sub as
    select  tran_date, tran_amt
    from alltran;
   
    proc sql;

    create table allnew as
    select * from alltran a
          where exists
                      (select avg(tran_amt) as avg_amt30 from sub b
                    where a.tran_date-30<=b.tran_date<=a.tran_date);
    quit;

I haven't got a chance to test it yet, but this is my idea, thank you so much!!

Message was edited by: Ming Jiang

Respected Advisor
Posts: 3,124

Re: Averaging amount spent per transaction over a month up to this transaction

Although we could use some of the educated guess, but to facilitate the answer you need, you may want to provide a sample (as fake as it can be, we don't care) of your input data, and a sample of your expected outcome. Don't have to be 30 days, we just need to know the data structure and your algorithm in action.

Regards,

Haikuo

Super User
Posts: 9,676

Re: Averaging amount spent per transaction over a month up to this transaction

proc sql ,  SET POINT= , array  all can achieve that goal . what is your sample data ? and the output you need.

Xia Keshan

Super User
Posts: 9,676

Re: Averaging amount spent per transaction over a month up to this transaction

But your output doesn't look like what you are talking about .

   obs  tran_date  tran_amt  mechr_cate_cd

    1    05/04/14   5.32      4633                    <-----------not '24may2014'd <= tran_date <= '24jun2014'd

    2    05/06/14   8.97      5846

    3    06/02/14   10.13     7996

data have;

input obs  tran_date :mmddyy10.  tran_amt  mechr_cate_cd ;

format tran_date mmddyy10.;

cards;

    1    05/24/14   5.32      4633

    2    05/26/14   8.97      5846

    3    06/02/14   10.13     7996

;run;

data want (drop=n sum);

set have;

if '24may2014'd <= tran_date <= '24jun2014'd then do;

   n+1;sum+tran_amt;

   avg_amt30=divide(sum,n);

end;

run;

Xia Keshan

New Contributor
Posts: 4

Re: Averaging amount spent per transaction over a month up to this transaction

Hi Keshan,

Thank you for your reply,

I meant that for EACH record, we find all the record in the past 30 days and let their average to be the avg_amt30. so the time interval for each record may be different and not uniform, for example,

for obs 2,

7.15=(=(5.32+8.97)/2

for obs 3,

8.14=(5.32+8.97+10.13)/3

sorry that I didn't make it clear earlier

SAS Employee
Posts: 340

Re: Averaging amount spent per transaction over a month up to this transaction

Not tested:

proc sql;

  create table want as

  select l.obs, l.tran_date, l.tran_amt, l.mechr_cate_cd, avg(r.tran_amt) as avg_last30

  from have l left join have r on (l.tran_date-30<=r.tran_date<=l.tran_date)

  group by 1,2,3,4

  ;

quit;

Don't you need this grouped by mechr_cate_cd or by some client_id?

Do you really want to indlude the current transsaction in the average? If not, change <= to < on the right side.

New Contributor
Posts: 4

Re: Averaging amount spent per transaction over a month up to this transaction

Hi Gergely,

Thank you for your help.,

your idea works!

and yes, I need to order by client_id and merch_id as well,

I think I need to include the current transaction, since for the first transaction, we need itself as its average, thank you for the reminder!!

Super Contributor
Posts: 1,636

Re: Averaging amount spent per transaction over a month up to this transaction

data have;

input id :tran_date : mmddyy8. tran_amt  mechr_cate_cd;

format tran_date mmddyy10.;

cards;

    1    05/04/14   5.32      4633

    1    05/06/14   8.97      5846

    1    06/02/14   10.13     7996

;

proc sql;

  create table temp as select a.id, a.tran_date as a_date, b.tran_date as b_date,b.tran_amt from have as a,have as b

    where a.id=b.id and a.tran_date-b.tran_date between 0 and 30

     order by id, a.tran_date,b.tran_date;

  create table want as

     select id,a_date,mean(tran_amt) as amt

    from temp

      group by id,a_date;

quit;

New Contributor
Posts: 4

Re: Averaging amount spent per transaction over a month up to this transaction

Hi Linlin,

Thank you for the tips of step by step instruction!

It worked well.

Super User
Posts: 9,676

Re: Averaging amount spent per transaction over a month up to this transaction

OK. Here is SQL. but if you care about Speed ,then using ARRAY or POINT= option:

data have;
input obs  tran_date :mmddyy10.  tran_amt  mechr_cate_cd ;
format tran_date mmddyy10.;
cards;
    1    05/24/14   5.32      4633
    2    05/26/14   8.97      5846
    3    06/02/14   10.13     7996
;run;
proc sql;
 create table want as
  select *,(select sum(tran_amt) from have where tran_date between intnx('month',a.tran_date,-1,'s')  and a.tran_date  )/(select count(tran_amt) from have where tran_date between intnx('month',a.tran_date,-1,'s')  and a.tran_date  )  as avg_amt30
   from have as a;
quit;

Xia Keshan

Ask a Question
Discussion stats
  • 9 replies
  • 498 views
  • 0 likes
  • 5 in conversation