Solved
Contributor
Posts: 28

# Find MAX date by customer

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?

Accepted Solutions
Solution
‎08-23-2016 08:39 AM
Super User
Posts: 10,209

## Re: Find MAX date by customer

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.

``````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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code

All Replies
Solution
‎08-23-2016 08:39 AM
Super User
Posts: 10,209

## Re: Find MAX date by customer

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.

``````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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Contributor
Posts: 28

## Re: Find MAX date by customer

Kurt,

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

thank you very much for your help,

best regards.

Super User
Posts: 10,209

## Re: Find MAX date by customer

paul_skirving wrote:

Kurt,

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

Which is the most important thing for me, also.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Super User
Posts: 13,498

## Re: Find MAX date by customer

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;

☑ This topic is solved.