Date:         Sun, 24 Jan 2010 05:45:20 -0800
Reply-To:     Arthur Tabachneck <art297@NETSCAPE.NET>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Arthur Tabachneck <art297@NETSCAPE.NET>
Subject:      Re: Proc SQL help needed please.
Comments: To: zoooom <zoooom@yahoo.co.uk>
In-Reply-To:  <n9W6n.4729$vL2.2261@newsfe21.ams2>
Content-Type: text/plain; charset=ISO-8859-1

You didn't provide any test data and desired results, thus I can only
guess whether the following is doing what you expect:

proc sql;
  create table table_a
    as select distinct a.*, min(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
             group by a.cust_id
;
quit;

HTH,
Art
----------------
On Jan 24, 6:28 am, "zoooom" <zoo...@yahoo.co.uk> wrote:
> 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;