Hi.
The below code retreives blank value in ACCOUNT_NUMBER field. Not sure where its going wrong.
The variables which are not in ABC and available in DEF (ACCOUNT_NUMBER,UPDATE_WHEN_PMT_CD_AUTO_CHANGE etc) are blank . I want ACCOUNT_NUMBER to be populated. Kindly advise. Thanks.
proc sql noprint;
create table TEMP as
select *
FROM ABC a left join (select *
FROM DEF b
where b.RATE_EFFECTIVE_DATE > ( select distinct reporting_date from ABC )
and b.reporting_date = ( select distinct reporting_date from ABC )
and UPCASE(strip(UPDATE_WHEN_PMT_CD_AUTO_CHANGE)) = 'N') c
on a.ACCT_NUMBER = c.Account_Number;
quit;
I think your query should read:
proc sql;
create table TEMP as
select a.*, b.*
FROM
ABC as a left join
( select * from DEF
where upcase(strip(UPDATE_WHEN_PMT_CD_AUTO_CHANGE)) = 'N')
as b on
a.ACCT_NUMBER = b.Account_Number and
a.reporting_date = b.reporting_date and
a.reporting_date < b.RATE_EFFECTIVE_DATE;
quit;
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.