Last. In proc SQL join

Reply
Occasional Contributor
Posts: 7

Last. In proc SQL join

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.

Super User
Posts: 3,257

Re: Last. In proc SQL join

The LAST.byvar variables are only available in a DATA step, not SQL. However in SQL you could do something like this (untested):

proc sql;

  create table want as

  select a.cust_id

  join

  (select cust_id

            ,max(transaction_date) as last_transaction_date

  from transactions

  group by cust_id) as b

  on a.cust_id = b.cust_id.

  ;

quit;

Super User
Posts: 7,832

Re: Last. In proc SQL join

Let's assume that table A has the customers (unique customer ID) and table B the transactions.

proc sort data=A;

by custid;

run;

/* unless A is already sorted */

proc sort data=B;

by custid trans_date;

run;

/* unless B is already properly sorted */

data want;

merge

  A (in=a)

  B (in=b)

;

by custid;

if a and b and last.custid;

run;

If table A is not unique for custid:

data B1;

set B;

by custid;

if last.custid;

run;

data want;

merge

  A (in=a)

  B1 (in=b)

;

by custid;

if a and b;

run;

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Moderator
Posts: 252

Re: Last. In proc SQL join

Ranjita,

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?

Regards,

Andrew.

Ask a Question
Discussion stats
  • 3 replies
  • 291 views
  • 0 likes
  • 4 in conversation