I am attempting to create a new table using proc sql that takes all the records from test_table and inner join it with items table on cust_id.

What I want to end up with with is a table that contains 1 record for each cust_id, and the flag_id associated with the min(order_date) returned after the order_date of 10/08/09.

I have tried various iterations of code and not been able to solve this problem - a slow death by syntax followed.

In the end I solved it using the below code, but is there a more efficient way to do this in one proc sql block using a subquery?

All replies greatly appreciated.

proc sql; create table table_a as select a.*, b.order_date, b.flag_id from test_table a, items b where a.cust_id = b.cust_id and b.order_date>= '10aug2009'd order by cust_id, order_date ;

data table_a; set table_a;

by cust_id; if first.cust_id; run;