Date:         Thu, 21 Aug 2003 16:25:40 +0200
Reply-To:     Gunnar Keen <gunnar.keen@DB.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Gunnar Keen <gunnar.keen@DB.COM>
Subject:      Antwort: Sql FIRST function
Comments: cc: Christopher Sadler <cjsadler@HOTMAIL.COM>
Content-Type: text/plain; charset="us-ascii"

Hi Chris,

if Names aren't of real interest you can use min or max functions in SQL
to overcome grouping problems caused by them.


data dataset1;
input id   vizperiod $   name $   viztotal;
cards;
1    qtr1        Bob         3
1    qtr1        Robert     4
3    qtr1        Jim          2
3    qtr1        James      3
run;

proc sql;
select id, vizperiod, max(name), min(name), sum(viztotal) as sumviz
from dataset1
group by id, vizperiod;
quit;

Kind Regards

Gunnar




Christopher Sadler <cjsadler@HOTMAIL.COM>
Gesendet von: "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
21.08.03 16:00
Bitte antworten an Christopher Sadler


        An:     SAS-L@LISTSERV.UGA.EDU
        Kopie:
        Thema:  Sql FIRST function


Is there some sort of equivalent in proc SQL for the FIRST function?   (In
non-SAS SQL, this function enables you to get the first value of a
variable
when using a 'group by' function).  Essentially, what I want to accomplish
is this:

Take this dataset:
id   vizperiod   name    viztotal
1    qtr1        Bob         3
1    qtr1        Robert     4
3    qtr1        Jim          2
3    qtr1        James      3

Turn in into this:
1    qtr1       Bob         7
3    qtr1       Jim          5

The ideal SQL would be something like this:
proc sql;
select id, vizperiod, first(name), sum(viztotal) as sumviz
from dataset1
group by id, vizperiod;

Right now, we don't really care that much about the name field.   We are
more interested in the summaries and will deal with the varied way that
people's names are entered later.  However, for the time being we do need
*some* name value.   But there is no FIRST function in SAS.   So how do I
deal with the name field?  (I'm only curious about whether this is
possible
in proc SQL-- I know there's plenty of ways to solve this problem with
regular SAS code)

Thanks,
Chris Sadler

_________________________________________________________________
<b>MSN 8:</b>  Get 6 months for $9.95/month
http://join.msn.com/?page=dept/dialup