05-06-2014 04:40 PM
I am trying to join 2 tables by customer id. I need to bring in the last transaction date from the transaction table using customer id. Can I directly include this using last. In proc SQL. If yes, can someone please give me the syntax for this.
05-06-2014 05:10 PM
The LAST.byvar variables are only available in a DATA step, not SQL. However in SQL you could do something like this (untested):
create table want as
,max(transaction_date) as last_transaction_date
group by cust_id) as b
on a.cust_id = b.cust_id.
05-07-2014 02:01 AM
Let's assume that table A has the customers (unique customer ID) and table B the transactions.
proc sort data=A;
/* unless A is already sorted */
proc sort data=B;
by custid trans_date;
/* unless B is already properly sorted */
if a and b and last.custid;
If table A is not unique for custid:
if a and b;
05-07-2014 03:24 AM
If your data is stored as SAS tables, then I would concur with the others & use First/Last within the Data step.
If you are hoping to utilise First/Last processing in SQL because your data resides in a database, then some databases do have variants of First/Last processing, meaning you could potentially achieve this using pass-thru SQL commands. However, the syntax is DB-specific, so if this is what you are after, can you give us more information on how the data is stored?