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
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.
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.
Kurt,
that works a treat and i think i understand how it works too!
thank you very much for your help,
best regards.
@paul_skirving wrote:
Kurt,
... and i think i understand how it works too!
Which is the most important thing for me, also.
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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.