Date: Tue, 27 Apr 2004 09:46:14 -0400
Reply-To: Sigurd Hermansen <HERMANS1@WESTAT.COM>
Sender: "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From: Sigurd Hermansen <HERMANS1@WESTAT.COM>
Subject: Re: SQL help needed: max(trans_date) for each account
Content-Type: text/plain
Richard:
I believe that this is the same problem that I reported a number of months
ago on SAS-L. The undocumented MONOTONIC() function does not appear in the
documentation for a reason. If invoked in a CREATE VIEW statement or an
in-line view, it seems that the MAX() and MIN() functions do not recognize
its calculated value. If instantiated in a prior CREATE TABLE or a Data
step, the MAX() etc. functions do recognize it.
As you know, assuming ordering by row ID's has its dangers in SQL queries. I
usually attribute the need to fix a row order as a DB design failure. Even
so, it often becomes a practical necessity.
Sig
-----Original Message-----
From: Richard A. DeVenezia [mailto:radevenz@IX.NETCOM.COM]
Sent: Monday, April 26, 2004 5:46 PM
To: SAS-L@LISTSERV.UGA.EDU
Subject: Re: SQL help needed: max(trans_date) for each account
Richard A. DeVenezia wrote:
> Cool wrote:
>> Using SQL, I want to select one record per account number (acct_id)
>> having the latest latest transaction date. I need the entire source
>> record (many columns), not just the max trans_date for each acct_id.
>> Can anyone point me in the right direction?
>>
>> Thanks a bunch,
>> Cool
>
> Typical transaction systems will have multiple rows on the given
> latest transaction date. What are the criteria for selecting a record
> when the latest date has more than one ?
> Usually there is satellite information such as time or transaction id
> that can be used. In your case, you don't state one, so we will use
> the undocumented SQL function monotonic() to obtain a unique
> sequenced value that can be applied in a criteria.
>
...
>
> proc sql;
> create table bar as
> select *, monotonic() as counter
> from foo
> group by account
> having date = max(date) /* an accounts max date might have many
> records */
> and counter = max(counter) /* but only one max counter within the
> account */
> ;
> quit;
This SQL is correct only when natural order is sorted by account and date.
If the data is disordered the query will return no rows when sorted by
descending date (When highest counter of an account group would correspond
to the oldest date [not the newest date as cool desires])
Here is corrected SQL where a subquery returns the sets of rows having group
account date=max(date) and then from therein is one row selected by the
criteria counter=max(counter) where counter is monotonic()
data foo;
retain seed 31415926;
do account = 1 to 100;
do date = today()-10 to today()-3*ranuni(seed);
do i = 1 to 10*ranuni(seed);
transid+1;
retain v1-v40 .;
output;
end;
end;
end;
stop;
format date date9.;
drop i seed;
run;
* disorder the data;
proc sql;
create table foo as
select * from foo
order by ranuni(1)
;
quit;
* The From sub-query ensures the outer having operates only on
* rows corresponding to max date of an account;
proc sql;
create table bar as
select monotonic() as counter, *
from (select * from foo group by account having date=max(date) ) /*
possibly many trans on max date */ group by account having counter =
max(counter) /* but only one max counter within the account max trans date
group */ ; quit;
proc sort data=foo;
by account descending date ;
run;
* Zero rows! Unexpected but true,
* Sorting by descending date causes max counter to occur on min(date); proc
sql; create table bar2 as select *, monotonic() as counter from foo group by
account having date = max(date) /* an accounts max date might have many
records */ and counter = max(counter) /* but only one max counter within the
account */ ; quit;
--
Richard A. DeVenezia http://www.devenezia.com/downloads/sas/macros/?m=xmlib
