topic Re: Find MAX date by customer in SAS Programming

Find MAX date by customer

pandhandj — Tue, 23 Aug 2016 12:07:57 GMT

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

Re: Find MAX date by customer

Kurt_Bremser — Tue, 23 Aug 2016 12:33:18 GMT

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;

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.

Re: Find MAX date by customer

pandhandj — Tue, 23 Aug 2016 12:39:08 GMT

Kurt,

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

thank you very much for your help,

best regards.

Re: Find MAX date by customer

Kurt_Bremser — Tue, 23 Aug 2016 12:43:22 GMT

@paul_skirving wrote:

Kurt,

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

Which is the most important thing for me, also.

Re: Find MAX date by customer

ballardw — Tue, 23 Aug 2016 15:47:37 GMT

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;