Date:         Tue, 18 Sep 2001 14:34:34 GMT
Reply-To:     Ace <b.rogers@VIRGIN.NET>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Ace <b.rogers@VIRGIN.NET>
Subject:      Re: SQL problem- need help

On 18 Sep 01 14:18:38 GMT, dward@SASHELP.COM (David L. Ward) wrote:

>On Tue, 18 Sep 2001 09:54:51 -0400, Conrad, Douglas
><Douglas.Conrad@FMR.COM> wrote:
>
>>Basically, I'm grabbing one variable from the join of two tables with many
>>conditions
>>and this variable then populates a new variable in another table.
>>
>>Currently the value that is taken is the min(ID) but that is wrong. It
>needs
>>to be the first ID based on the EARLIEST DATE of another field. (basically
>>the first person who enters the info needs to be the critical ID and is
>>tracked in the other table.)
>
>
>Doug,
>  My first impression of what you are asking is that you need to mimic the
>first.variable behavior available in the data step.  Using SQL I don't
>think it's either very straightforward or efficient - why not use a data
>step or two.

It should be fairly straightforward with a subquery. I haven't got
time to work it through atm, but something like :

select oper_id, date from table1 where date in (select min(date) from
tablea a, tableb b where ......... ) ;

should do the trick, I reckon. Perhaps you'd need a GROUP BY in the
subquery too, unless it's actually just a single value you need.

HTH

--
Ace in Basel