Date:         Tue, 7 Sep 2010 08:43:58 -0500
Reply-To:     Joe Matise <snoopy369@GMAIL.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Joe Matise <snoopy369@GMAIL.COM>
Subject:      Re: Help - SQL
Comments: To: Steve Steve <steve11145@yahoo.com>
In-Reply-To:  <940669.15493.qm@web113620.mail.gq1.yahoo.com>
Content-Type: text/plain; charset=ISO-8859-1

You don't have DT on the results dataset.  While you can filter using WHERE
on a value that does not appear on the resulting dataset, you cannot do so
using HAVING as HAVING explicitly filters the results dataset on itself and
does not reference the original dataset.  Also, the DISTINCT is not useful
here; that's what GROUP BY does, after all.

This would work:

data test;
input id dt date7. val;
cards;
1 02SEP09 6
1 09SEP09 7
1 09SEP09 3
2 11AUG09 2
2 18AUG09 9
;
run;
proc sql;
    create table new as
    select id, val2 from (
     select id, dt
           ,mean(val) as val2
     from test
     group by id, dt)
     group by id
     having dt=max(dt)
     order by id
;
quit;

It is a bit early in the morning for me to give you the truly correct
solution [as in, one that does not give the REMERGING warning].  I'm fairly
sure it's easy to do, just beyond my early morning inadequately caffeinated
brain.

... and now it kicks in :)  Use a WHERE-EXISTS clause:

proc sql;
    create table new as
     select id
           ,mean(val) as val2
     from test T
     where dt = (select max(dt) from test F where T.id=F.id)
     group by id
     order by id
;
quit;

if the data is not too gigantic in any event.  A join to a pre-run table
with max(dt) and id would be faster in some instances I suspect if it's a
huge dataset.

Thanks,

Joe

On Tue, Sep 7, 2010 at 8:27 AM, Steve Steve <steve11145@yahoo.com> wrote:

> What is wrong with the "having dt=max(dt)"? It does not work.
>
> Thanks for your help!
>
> data test;
> input id dt date7. val;
> cards;
> 1 02SEP09 6
> 1 09SEP09 7
> 1 09SEP09 3
> 2 11AUG09 2
> 2 18AUG09 9
> ;
> run;
> proc sql;
>      create table new as
>      select distinct id
>            ,mean(val) as val2
>      from test
>      group by id
>      having dt=max(dt)
>      order by id
> ;
> proc print;
>
> Obs    id      val2
>  1      1    5.33333 (should be 5)
>  2      2    5.50000 (should be 9)
>
>
>
>