BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
podarum
Quartz | Level 8

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

1 ACCEPTED SOLUTION

Accepted Solutions
art297
Opal | Level 21

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

2 REPLIES 2
art297
Opal | Level 21

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
Quartz | Level 8

Many thanks

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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