LISTSERV at the University of Georgia
Menubar Imagemap
Home Browse Manage Request Manuals Register
Previous messageNext messagePrevious in topicNext in topicPrevious by same authorNext by same authorPrevious page (August 2003, week 4)Back to main SAS-L pageJoin or leave SAS-L (or change settings)ReplyPost a new messageSearchProportional fontNon-proportional font
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
Comments: To: Puddin' Man <pudding_man@POSTMARK.NET>
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


Back to: Top of message | Previous page | Main SAS-L page