Date:         Mon, 25 Jan 2010 17:47:18 -0000
Reply-To:     zoooom <zoooom@YAHOO.CO.UK>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         zoooom <zoooom@YAHOO.CO.UK>
Organization: virginmedia.com
Subject:      Re: Proc SQL help needed please.
Comments: To: sas-l@uga.edu
In-Reply-To:  <FE10F31634E7F34B87AA143D59608541405C3B84@EX-CMS01.westat.com>
Content-Type: text/plain; format=flowed; charset="iso-8859-1";
              reply-type=original

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;