f



SQL help needed: max(trans_date) for each account

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
0
cool_guy (13)
4/23/2004 7:44:20 PM
comp.soft-sys.sas 142828 articles. 3 followers. Post Follow

3 Replies
392 Views

Similar Articles

[PageSpeed] 32

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.

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;

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;


-- 
Richard A. DeVenezia
http://www.devenezia.com/downloads/sas/macros/


0
radevenz (1543)
4/24/2004 3:24:17 AM
At 03:44 PM 4/23/2004, 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.

Well, the current ethos does seem to be to use SQL wherever possible.
But there's always,

PROC SORT DATA=ACCOUNT.TRANSACT OUT=BY_ID;
   BY ACCT_ID DESCENDING TRANS_DATE;
RUN;
/* To select ONE record (unspecified)    */
/* on the latest transaction date        */
DATA LASTDATE;
  SET BY_ID;
   BY ACCT_ID DESCENDING TRANS_DATE;
   IF FIRST.ACCT_ID THEN OUTPUT;
RUN;
/* To select ALL records on the latest  */
/* transaction date                     */
DATA LASTDATE;
  SET BY_ID;
   BY ACCT_ID DESCENDING TRANS_DATE;
   RETAIN TAKE_IT;
   DROP   TAKE_IT;
   IF      FIRST.ACCT_ID    THEN TAKE_IT = 1.
   ELSE IF FIRST.TRANS_DATE THEN TAKE_IT = 0.
   IF TAKE_IT = 1 THEN OUTPUT.
RUN;
0
wrristow (97)
4/25/2004 4:05:34 AM
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


0
radevenz (1543)
4/26/2004 9:46:17 PM
Reply: