get the first entry of a series using proc sql

[podarum]

Hi,  I'm merging 2 files.  One is unique account information anf the second is relevant detailed account information from the datawarehouse that contains snapshots of the accounts at the end of every month. So for example account 0023 in file 1 will have 25 entries in file 2.  Once I link these to files using account number, I only want to get the information on file 2 of the first entry (of the default month).  There's

Here's what I mean.

File1

Acct_No     Claim

234               $40

256               $50

File2

Acct_No    Deflt_Dt          Deflt_Ind   Balance     Snap_dat

234                   .                       N               $20          2010/08/30

234                   .                       N               $25          2010/09/30

234               2010/10/30          Y               $35          2010/10/30

234               2010/10/30          Y               $35          2010/11/30

256                  .                        N               $10          2009/03/30

256                  .                        N               $20          2009/04/30

256               2009/05/30          Y               $30          2009/05/30

256               2009/05/30          Y               $30          2009/06/30

WANT

File3

Acct_No     Deflt_Dt          Deflt_Ind   Balance     Snap_dat       Claim

234            2010/10/30               Y            $35          2010/10/30       $40

256            2009/05/30               Y            $30          2009/05/30       $50

[art297 · Accepted solution]

proc sql;

  create table File3 as

    select *

      from File2  (where=(not missing(Deflt_Dt))) as a

        left join File1 as b

          on a.Acct_No eq b.Acct_No

            group by Acct_No

              having a.Deflt_Dt eq min(a.Deflt_Dt) and

                a.Snap_Dat eq min(a.Snap_Dat)

  ;

quit;

[podarum]

Many thanks