Date:         Mon, 4 Dec 2006 16:31:52 -0500
Reply-To:     Sigurd Hermansen <HERMANS1@WESTAT.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Sigurd Hermansen <HERMANS1@WESTAT.COM>
Subject:      Re: There's something about HAVING (Re: Selecting nearest value
              in time)
Comments: To: Dimitri Shvorob <dimitri.shvorob@vanderbilt.edu>
In-Reply-To:  <457489D7.4050309@vanderbilt.edu>
Content-Type: text/plain; charset="us-ascii"

Dimitri:
The closest to the Russian adage that I can recall offhand is "There's
nothing new under the sun". The remerge feature allows columns in the
query yield even though they do not appear in the GROUP BY clause. Use
with caution!
Sig

-----Original Message-----
From: Dimitri Shvorob [mailto:dimitri.shvorob@vanderbilt.edu]
Sent: Monday, December 04, 2006 3:49 PM
To: SAS-L@LISTSERV.UGA.EDU
Cc: Sigurd Hermansen
Subject: Re: There's something about HAVING (Re: Selecting nearest value
in time)


Sigurd, thank you for the welcome historic perspective :)  - I am
forgetting the English equivalent, but back in Russia they say 'The new
is the forgotten old'.  I hope that SAS Institute clarifies HAVING's
(peculiar) capabilities in future versions of the PROC SQL manual.

PS. I did a quick check on whether the (SAS-specific?) HAVING trick
would work in MySQL - no.

id    date
 +---+ +----------+
  687   1999-01-20
  685   1999-01-27
  938   1999-01-27
  266   1999-01-29
  677   2000-01-20
  687   2000-01-20
  0     2000-01-20
  938   2000-01-21
  685   2000-01-27
  266   2000-01-31
  547   2001-01-03

 id    date
 +---+ +----------+
  677   2000-12-14
  547   2000-12-15
  938   2000-12-18

create table temp3 as select a.id, a.date as date1, b.date as date2 from
temp1 a left join temp2 b on a.id = b.id group by a.id having abs(a.date
- b.date) = min(abs(a.date - b.date))

  id    date1        date2
 +---+ +----------+ +----------+
  547   2001-01-03   2000-12-15
  677   2000-01-20   2000-12-14