Help using Base SAS procedures

get the first entry of a series using proc sql

Accepted Solution Solved
Reply
Super Contributor
Posts: 401
Accepted Solution

get the first entry of a series using proc sql

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


Accepted Solutions
Solution
‎09-13-2014 05:06 PM
PROC Star
Posts: 7,471

Re: get the first entry of a series using proc sql

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;

View solution in original post


All Replies
Solution
‎09-13-2014 05:06 PM
PROC Star
Posts: 7,471

Re: get the first entry of a series using proc sql

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;

Super Contributor
Posts: 401

Re: get the first entry of a series using proc sql

Many thanks

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 2 replies
  • 178 views
  • 0 likes
  • 2 in conversation