Difference between two variables

[Solly7]

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

[Solly7]

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

[Kurt_Bremser]

Please do not double-post.

[Solly7]

Apologies for double-posting

[Solly7]

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

[novinosrin]

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;

[Kurt_Bremser]

Use lag():

data want;
set have;
by policy_no;
age = intck('month',lag(date_received),date_received);
if last.policy_no;
run;

[Solly7]

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

[Kurt_Bremser · Accepted solution]

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;

[Solly7]

Thank you very much..it works!