DATA Step, Macro, Functions and more

Find MAX date by customer

Accepted Solution Solved
Reply
Contributor
Posts: 28
Accepted Solution

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?

 

thanks in advance


Accepted Solutions
Solution
‎08-23-2016 08:39 AM
Super User
Posts: 7,766

Re: Find MAX date by customer

Posted in reply to pandhandj

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers

View solution in original post


All Replies
Solution
‎08-23-2016 08:39 AM
Super User
Posts: 7,766

Re: Find MAX date by customer

Posted in reply to pandhandj

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Contributor
Posts: 28

Re: Find MAX date by customer

Posted in reply to KurtBremser

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: 7,766

Re: Find MAX date by customer

Posted in reply to pandhandj

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
Super User
Posts: 11,343

Re: Find MAX date by customer

Posted in reply to pandhandj

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.

Need further help from the community? Please ask a new question.

Discussion stats
  • 4 replies
  • 290 views
  • 0 likes
  • 3 in conversation