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;
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.