Let's dissect your first step:
data INSURANCE_CASHFLOW_updated2 (drop=ISSUE_MONTH_old);
set INSURANCE_CASHFLOW (where=(REPORTING_CAUSE="A"));
length ISSUE_MONTH $72.; /* why are you wasting 65 empty bytes when you only store 7 bytes in the string? */
ISSUE_MONTH_old=put(ISSUE_DT,yymmn6.);
/* Read the documentation, and you will see that the "n" in the format stands for "no delimiter" */
/* Use yymmd7. in the next statement (d = dash) */
ISSUE_MONTH=cats(substr(ISSUE_MONTH_old,1,4),"-",substr(ISSUE_MONTH_old,5));
/* should be: */
ISSUE_MONTH = put(ISSUE_DT,yymmd7.);
run;
But you only need this new variable for joining, so you can move that into the "on" clause in the SQL.
Let's take a look at your third step:
data temp_final (drop=ISSUE_MONTH INITIAL_RECOGNITION_DT);
set temp3;
INSURANCE_CONTRACT_GROUP_ID=compress(cats(INSURANCE_CONTRACT_GROUP_ID,put(ISSUE_DT,date9.),put(INITIAL_RECOGNITION_DT,date9.)),.);
/* compress() expects a character value as its second argument, so the numerical missing value is wrong here */
/* if you want to get rid of dots caused by missing numerical values, use '.' as second argument */
/* This can be moved into the SQL also */
run;
Creation of the new variable can also be moved into the SQL.
So I propose to try this:
proc sql;
create table temp_final as
select
a.*,
b.INITIAL_RECOGNITION_DT,
compress(cats(a.INSURANCE_CONTRACT_GROUP_ID,put(a.ISSUE_DT,date9.),put(b.INITIAL_RECOGNITION_DT,date9.)),'.')
as INSURANCE_CONTRACT_GROUP_ID
from INSURANCE_CASHFLOW as a
left join ADP_OUTPUT_NON_LIFE as b
on
a.REPORTING_DT = b.REPORTING_DT and
a.ENTITY_ID = b.UNIT and
put(a.ISSUE_DT,yymmd7.) = b.ISSUE_MONTH
;
quit;
PS this code is of course untested, for lack of usable test data.
