BookmarkSubscribeRSS Feed
Babloo
Rhodochrosite | Level 12
 
1 REPLY 1
Kurt_Bremser
Super User

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. 

hackathon24-white-horiz.png

The 2025 SAS Hackathon has begun!

It's finally time to hack! Remember to visit the SAS Hacker's Hub regularly for news and updates.

Latest Updates

How to Concatenate Values

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.

SAS Training: Just a Click Away

 Ready to level-up your skills? Choose your own adventure.

Browse our catalog!

Discussion stats
  • 1 reply
  • 675 views
  • 2 likes
  • 2 in conversation