Date:         Thu, 11 Oct 2007 23:27:45 -0700
Reply-To:     beatrice <beatriceghitoiu@YAHOO.COM>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         beatrice <beatriceghitoiu@YAHOO.COM>
Organization: http://groups.google.com
Subject:      Group by in PROC SQL
Comments: To: sas-l@uga.edu
Content-Type: text/plain; charset="iso-8859-1"

Hi,

I need to select an effective_rate on an account, that has multiple
entries with different dates.  I need the entry with the latest date.
This is what Im writing:

* RSUBMIT;
PROC SQL NOPRINT;
        CONNECT TO ORACLE       (USER           = &unouser.
                                                 PASS           = &unopass.
                                                 PATH           = &unonode.
                                                 BUFFSIZE       = 32767
                                                );

        CREATE TABLE MyTable AS

                SELECT * FROM CONNECTION TO ORACLE
                        (

                        SELECT DISTINCT  ORIGINAL.ACCOUNT_NUMBER, ORIGINAL.EFFECTIVE_RATE

                        FROM Table1 PARTITION  (INFORMATION_DATE_&DDMMYY.) ORIGINAL ,

                       (SELECT ACCOUNT_NUMBER , max(RATE_START_DATE) as
RATE_START_DATE

                                FROM Table1  PARTITION (INFORMATION_DATE_&DDMMYY.)
                                      GROUP BY ACCOUNT_NUMBER) maxresults

                WHERE ORIGINAL.ACCOUNT_NUMBER = maxresults.ACCOUNT_NUMBER AND
ORIGINAL.RATE_START_DATE =  MAXRESULTS.RATE_START_DATE

);


DISCONNECT FROM ORACLE;
QUIT;

when I run the inner select I get back 5679 rows, and when i run the
whole block of code get 9361 rows.  Does this mean that there are more
entries with the same account number as well as the same
Rate_start_date?

thank you,

Beatrice