The SQL solution that Art posted will come very close to the results of the SAS SQL/Data step solution that you found. Art's solution will leave more than one order per customer in the yield of the query when any one customer has more than one order per day in the database and that order has a different flag value.

As I said often, the arbitrary nature of the first.x or SORT DEDUPKEY method of deduplication leads to uncomfortable compromises. You won't find an SQL equivalent of the first.x method. SQL, to its credit, doesn't arbitrarily discard information.
S

-----Original Message-----
From: SAS(r) Discussion [mailto:SAS-L@LISTSERV.UGA.EDU] On Behalf Of zoooom
Sent: Sunday, January 24, 2010 6:29 AM
To: SAS-L@LISTSERV.UGA.EDU
Subject: Proc SQL help needed please.

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;

that's a really helpful answer, thanks a lot.

"Sigurd Hermansen" <HERMANS1@WESTAT.COM> wrote in message 
news:FE10F31634E7F34B87AA143D59608541405C3B84@EX-CMS01.westat.com...
> The SQL solution that Art posted will come very close to the results of 
> the SAS SQL/Data step solution that you found. Art's solution will leave 
> more than one order per customer in the yield of the query when any one 
> customer has more than one order per day in the database and that order 
> has a different flag value.
>
> As I said often, the arbitrary nature of the first.x or SORT DEDUPKEY 
> method of deduplication leads to uncomfortable compromises. You won't find 
> an SQL equivalent of the first.x method. SQL, to its credit, doesn't 
> arbitrarily discard information.
> S
>
> -----Original Message-----
> From: SAS(r) Discussion [mailto:SAS-L@LISTSERV.UGA.EDU] On Behalf Of 
> zoooom
> Sent: Sunday, January 24, 2010 6:29 AM
> To: SAS-L@LISTSERV.UGA.EDU
> Subject: Proc SQL help needed please.
>
> 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;