Date:         Tue, 3 May 2005 07:13:01 -0700
Reply-To:     "Terjeson, Mark (IM&R)" <Mterjeson@RUSSELL.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         "Terjeson, Mark (IM&R)" <Mterjeson@RUSSELL.COM>
Subject:      Re: How to do if first.var1 in proc sql?
Content-Type: text/plain; charset="us-ascii"

Hi All,

Arjen is right that 8.2 will not care for
DISTINCT and COUNT(*) at the same time.
It is true that SQL does not have a first.
last. capability, however, two methods
come to mind how to simulate first. and
last. functionality in SQL.  These are 8.2
available.

First, if you do a preliminary step and
add a sequential number as a new column
into the dataset via a datastep such as
using _N_ you can simulate first. and
last. functionality.


data foo;
    v1='Barney'; v2=3; output; * first.v1 ;
    v1='Barney'; v2=2; output;
    v1='Barney'; v2=1; output;
    v1='Fred  '; v2=1; output; * first.v1 ;
    v1='Fred  '; v2=2; output;
    v1='Fred  '; v2=3; output;
    v1='Fred  '; v2=3; output;
    v1='Wilma '; v2=2; output; * first.v1 ;
    v1='Wilma '; v2=1; output;
run;


data foobar;
    set foo;
    n = _N_; * add sequential numbering ;
run;


    * simulate first.v1 ;
proc sql;
      create table d0 as
      select v1, v2, n
      from foobar
      group by v1
      having n=min(n)
      order by v1
    ;
quit;



Also, the MONOTONIC() function is a SAS SQL
alternative to the datastep _N_.  Obviously,
MIN() is going to give you first. and MAX()
is going to give you last. capability.



    * simulate first.v1 ;
proc sql;
      create table d1 as
      select v1, v2, monotonic() as pop
      from foo
      group by v1
      having pop=min(pop)
      order by v1
    ;
quit;


For either method you must know your data
content to insure the behavior you desire.



Hope this is helpful.


Mark Terjeson
Senior Programmer Analyst, IM&R
Russell Investment Group


Russell
Global Leaders in Multi-Manager Investing





-----Original Message-----
From: SAS(r) Discussion [mailto:SAS-L@LISTSERV.UGA.EDU] On Behalf Of
Arjen Raateland
Sent: Tuesday, May 03, 2005 5:36 AM
To: SAS-L@LISTSERV.UGA.EDU
Subject: Re: How to do if first.var1 in proc sql?


Sigurd Hermansen wrote:
> Rusty:
> Unless I am missing something, your program will select the least
> common combination of v1 with a v2 value.
>
> In SQL, the HAVING clause sets constraints on summary values. Try ....
>     proc sql;
>
>         create table d1 as
>         select distinct v1, v2, count(*) as pop
>         from d
>         group by v1 having pop=max(pop)
>         order by v1
>
>     quit;


Hello Sig,

I tried your code, but SAS 8.2 doesn't think it's acceptable:

65    proc sql;
66      create table d1 as
67      select distinct v1, v2, count(*) as pop
68      from foo
69      group by v1 having pop=max(pop)
70      order by v1
71    ;
ERROR: Summary functions nested in this way are not supported.
72    quit;
NOTE: The SAS System stopped processing this step because of errors.

It looked like a nice idea, though. Could you get it to work?

cheerio,
--
Mr. Arjen Raateland
Finnish Environment Institute SYKE
Research Dept.
SAS Support
phone +358 9 4030 0350