Find MAX date by customer

[pandhandj]

All,

i'm stuck again, maybe someone could help me out please?

I have the following, which gives me all the data i need, customer number, address etc.  it has 389 customers and 83,000 transaction dates.

proc sql;
     create table work.transaction_dates as
      select distinct
      t1.*,
      t2.Datestart as Last_Transaction_Date,
      t2.customer_number
           from work.have4 as t1 left join have5 as t2 on (t1.accountid=t2.a_number);
quit;

this works OK.

When i try to find the latest transaction date per customer with this

data work.transactions_MAX;
    set work.transaction_dates;
        where Last_Transaction_Date=max(Last_Transaction_Date);
        by Last_Transaction_Date;
run;

I get - ERROR: Function MAX requires at least 2 argument(s).  (the number of transactions per customer could be between 0 and X.)

How can i get the last transaction date per customer?

thanks in advance

[Kurt_Bremser · Accepted solution]

In a data step, the max(function) behaves differently from SQL. In SQL, max() works vertically and only needs one argument; in a data step, max() can only be applied to values of the current observation, and needs more than one argument.

Instead consider this:

proc sql;
select * from transaction_dates
group by customer_number
having last_transaction_date = max(last_transaction_date);
quit;

or

proc sort data=transaction_dates;
by customer_number descending last_transaction_date;
run;

data want;
set transaction_dates;
by customer_number;
retain ref_date;
if first.customer_number then ref_date = last_transaction_date;
if last_transaction_date = ref_date;
drop ref_date;
run;

Am not so sure about my SQL, but quite sure about the data step solution.

[pandhandj]

Kurt,

that works a treat and i think i understand how it works too!

thank you very much for your help,

best regards.

[Kurt_Bremser]

---

@paul_skirving wrote:

Kurt,

... and i think i understand how it works too!

---

Which is the most important thing for me, also.

[ballardw]

Depending on how many other variables you need to carry along;

proc summary data=work.transaction_dates nway;

   class customer_number;

   var ref_date;

   output out=work.lasttransaction max(ref_date)=Last_Transaction_date;

run;