Date: Fri, 9 Jul 1999 17:51:38 -0400
Reply-To: GORRELP1 <GORRELP1@WESTAT.COM>
Sender: "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From: GORRELP1 <GORRELP1@WESTAT.COM>
Subject: NOTSORTED Option: (WAS) :FIRST. and LAST. in PROC SQL
Content-Type: text/plain; charset=US-ASCII
This is a bit off-track, but this thread brought to mind a question. If you use
the NOTSORTED option with a BY statement, SAS generates FIRST. and LAST.
variables. My question is this: when and how does SAS generate these variables
with a regular SORT and BY statement, and when and how does it do so with the
NOTSORTED option? For example, is some form of lookahead used to set the LAST.
variable? I can imagine ways, but I'm curious how SAS actually does it.
The discussion in the Language Reference Manual (pp. 134ff.) begs the question.
Paul Gorrell
gorrelp1@westat.com
____________________Reply Separator____________________
Subject: (MVS) Re:FIRST. and LAST. in PROC SQL
Author: RAITHEM <RAITHEM@WESTAT.COM>
Date: 07/09/1999 4:41 PM
The inimitable Dr. Ray Pass posted the following:
>Well I got what I needed. A couple of respondents (John Gerlach, Erik
>Larsen) pointed out correctly and astutely that SQL really doesn't know or
>care about internal order of observations because of its truly relational
>nature. Nevertheless, one can "order" the obs for display using ORDER BY.
>
>I did get a proposed method from a few of the gods (Ian and Sig) at what my
>friend MVS Raithel calls "SAS Mecca". The suggestion was to use MIN and/or
>MAX functions in PROC SQL to pick out lowest and/or highest values. I
>think I'll do a post-SQL PROC SORT. Seems like I lose nothing with this
>method, and gain good old FIRST. and LAST. vars that I know and love
>(besides, Ian agrees!) Thanks all.
>
Ray, since you were kind enough to actually refer to me as your friend in
public, the least I can do is to give you my own solution to your PROC SQL
blues. So, hold everything; _DO_NOT_ reach for that SORT procedure just yet!
I would use my old comrade in arms SORTEDBY to assert that the data set _WAS_
sorted after it came out of PROC SQL. I would chamber one SORTEDBY round into
the waiting barrel of PROC DATASETS to assert the sort in the quietest,
sneakiest, least resource intensive way. Here is how it might look:
**************************************************************;
* Create the table with PROC SQL *;
**************************************************************;
proc sql;
create table testdata as
select d_race, sum(c_days) as sumcdays, sum(f_days) as sumfdays
from UNIXLIB.summ1
order by d_race;
quit;
**************************************************************;
* Assert the sort order with PROC DATASETS *;
**************************************************************;
proc datasets library=work;
modify testdata (sortedby=d_race);
run;
**************************************************************;
* Use my friendly First. variable... *;
**************************************************************;
data mike;
set testdata;
by d_race;
if first.d_race then put 'first d_race = ' d_race;
run;
Ray, as your practiced eye has undoubtedly already deduced, the first SAS step,
above, extracts my data and sorts it by D_RACE via the ORDER BY SAS SQL
statement. My DATASETS procedure then asserts the sort on the TESTDATA data set
via the MODIFY statement. If you were to run a CONTENTS procedure on the
TESTDATA data set after this point, you would see that the SAS System for
Information Delivery considers that the data set _IS_ now sorted. However,
since it is an asserted sort, and not the product of a PROC SORT, the CONTENTS
listing will read: "Validated: NO". The last SAS step, above, exploits the
fruit of my labor and uses the FIRST. variable.
Ray; you've just gotta love the SAS System for Information Delivery! There
always seems to be a way to do... anything! Best of luck in implementing a
low-resource intensive solution; I hope that there is no sequel to your SQL
problem!
I hope that this answer proves helpful now, and in the future!
Of course, all of these opinions and insights are my own, and do not reflect
those of my organization or my associates.
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
Michael A. Raithel
"The man who wrote the book on performance."
E-mail: raithem@westat.com
Author: Tuning SAS Applications in the MVS Environment
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
I invent nothing. I rediscover. -- Auguste Rodin
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
