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
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.
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.