Date: Fri, 22 Aug 2003 08:31:59 -0400
Reply-To: Ian Whitlock <WHITLOI1@WESTAT.COM>
Sender: "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From: Ian Whitlock <WHITLOI1@WESTAT.COM>
Subject: Re: Sql FIRST function
Content-Type: text/plain
Puddin',
I did miss the fact that he wanted the column summed. However, both the
title and first sentence do indicate a desire to work with an odered file in
SQL. Given those conditions and the way SQL is designed to be order free, I
suggest that adding a variable is essential.
Now how would you pick out the last name or the third name in each group?
IanWhitlock@westat.com
-----Original Message-----
From: Puddin' Man [mailto:pudding_man@POSTMARK.NET]
Sent: Thursday, August 21, 2003 5:30 PM
To: SAS-L@LISTSERV.UGA.EDU
Subject: Re: Sql FIRST function
Ian Old Bean,
Do you really define a "good data structure" as one
in which a new field must be created solely so that
a subsequent program structure (SQL step in this
case) can grab a value from the first record in
a group? :-)
Also, your code doesn't sum the 'viztotal' data.
SQL is good for lots of things. I don't know that
this is one of 'em. I assume Chris knows
what to do with this in a data step. Can Chris
name more than one vendor's SQL implementation
that supports a FIRST function? I don't think
FIRST is ansi ...
Prost,
Puddin'
*******************************************************
*** Puddin' Man *** Pudding_Man@postmark.net *****
*******************************************************;
"Well, mama don't allow no Boogie-Woogie round here
Mama don't allow no Boogie-Woogie round here
Well we don't care what mama don't allow
We're gonna Boogie-Woogie anyhow
Mama don't allow no Boogie-Woogie round here "
- Traditional
Ian Whitlock wrote:
> Chris,
>
> With a good data structure it is trivial.
>
> data w ; input id vizperiod $ name $ viztotal ;
> if id ^= lag(id) then seq = 0 ;
> seq + 1 ;
> cards ;
> 1 qtr1 Bob 3
> 1 qtr1 Robert 4
> 3 qtr1 Jim 2
> 3 qtr1 James 3
> ;
>
> proc sql ;
> select id , vizperiod , name, viztotal
> from w
> where seq = 1 ;
> quit ;
>
> Without, one can use the MONOTONIC function to get the required
> structure. See SAS-L arhcives www.sas-l.com. If I remember correctly,
> Ya Huang gave a nice answer in the last 6 months. Search for
> "monotonic" and "group" in
the
> text.
>
> IanWhitlock@westat.com
>
> -----Original Message-----
> From: Christopher Sadler [mailto:cjsadler@HOTMAIL.COM]
> Sent: Thursday, August 21, 2003 10:00 AM
> To: SAS-L@LISTSERV.UGA.EDU
> Subject: 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
| 
