topic Re: Difference between two variables in SAS Programming

Difference between two variables

Solly7 — Thu, 16 Apr 2020 10:57:52 GMT

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

Lapse_score difference

Solly7 — Thu, 16 Apr 2020 11:08:17 GMT

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

Re: Difference between two variables

Kurt_Bremser — Thu, 16 Apr 2020 11:11:38 GMT

Use lag():

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

Re: Lapse_score difference

Kurt_Bremser — Thu, 16 Apr 2020 11:18:48 GMT

Please do not double-post.

Re: Lapse_score difference

Solly7 — Thu, 16 Apr 2020 11:36:02 GMT

Apologies for double-posting

Re: Lapse_score difference

Solly7 — Thu, 16 Apr 2020 12:36:41 GMT

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

Re: Difference between two variables

Solly7 — Thu, 16 Apr 2020 12:45:48 GMT

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

Re: Difference between two variables

Kurt_Bremser — Thu, 16 Apr 2020 13:26:31 GMT

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;

Re: Difference between two variables

Solly7 — Thu, 16 Apr 2020 13:42:57 GMT

Thank you very much..it works!

Re: Lapse_score difference

novinosrin — Thu, 16 Apr 2020 14:22:30 GMT

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;