Optimization

Babloo — Wed, 28 Aug 2019 11:39:27 GMT

Re: Optimization

Kurt_Bremser — Tue, 27 Aug 2019 11:24:02 GMT

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.

topic Re: Optimization in SAS Programming

Optimization

Re: Optimization