Hi, i need help in extracting latest lapse_score and adding another variable into my data and calculate age in months of that latest lapse_score(i.e difference between extracted latest lapse_score and previous lapse score) .If the are three different(date_received) for one policy, i just need the difference between latest two dates. And if previous lapse score is null, then return 0 as difference in months. dot represent null received date
Data Have
itc_data table
policy_no date_received lapse_score
1 01Jan2018 30
1 01Apr2018 70
1 01Jun2018 20
2 01May2019 60
2 01Oct2019 80
Data Want
policy_no date_received lapse_score Age(Months)
1 01Jun2018 20 2
2 01Oct2019 80 5
Take care of that by checking for first.:
data want;
set have;
by policy_no;
age = intck('month',lag(date_received),date_received);
if last.policy_no;
if first.policy_no then age = 0;
run;
Hi, i need help in extracting latest lapse_score and adding another variable into my data and calculate age in months of that latest lapse_score(i.e difference between extracted latest lapse_score and previous lapse score) .If the are three different(date_received) for one policy, i just need the difference between latest two dates.
Data Have
itc_data table
policy_no date_received lapse_score
1 01Jan2018 30
1 01Apr2018 70
1 01Jun2018 20
2 01May2019 60
2 01Oct2019 80
Data Want
policy_no date_received lapse_score Age(Months)
1 01Jun2018 20 2
2 01Oct2019 80 5
Apologies for double-posting
Hi your solutions works but i still have a problem with policy numbers which consist of only 1 row(date_received). I need those particular rows to return 0 as Age since they do not have matching previous row. see example below.
Data Have
itc_data table
policy_no date_received lapse_score
3 01Jan2020 30
3 01Apr2020 70
4 01Jun2018 20
Data Want
policy_no date_received lapse_score Age(Months)
3 01Apr2020 70 2
4 01Jun2018 20 0
data want;
do until(last.policy_no);
set have;
by policy_no;
if first.policy_no and last.policy_no then _n_=date_received;
age=intck('mon',_n_,date_received);
_n_=date_received;
end;
run;
Use lag():
data want;
set have;
by policy_no;
age = intck('month',lag(date_received),date_received);
if last.policy_no;
run;
Take care of that by checking for first.:
data want;
set have;
by policy_no;
age = intck('month',lag(date_received),date_received);
if last.policy_no;
if first.policy_no then age = 0;
run;
Thank you very much..it works!
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.