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
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;
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;
Many thanks
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.