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
>>Basically, I'm grabbing one variable from the join of two tables with many
>>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
>>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.)
> 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.
Ace in Basel