## re: calculating number of days between successive dates

Solved
Regular Contributor
Posts: 247

# re: calculating number of days between successive dates

Hi...I am trying to calculate the number of days, weeks, months and years between successive claims for each ID. I am getting some unusual results. This is the code I am using:

proc sort data=Summary;

by ID Claim_Date;

run;

data pc1;

set Summary;

by ID;

if first.ID then do;

Number_Days = .;

Number_Weeks = .;

Number_Months = .;

Number_Years = .;

end;

else if ID = lag(ID) then do;

Number_Days = (intck('Day', lag(input(put(Claim_Date,8.),yymmdd8.)), input(put(Claim_Date,8.),yymmdd8.),'c'));

Number_Weeks = (intck('Week', lag(input(put(Claim_Date,8.),yymmdd8.)), input(put(Claim_Date,8.),yymmdd8.),'c'));

Number_Months = (intck('Month', lag(input(put(Claim_Date,8.),yymmdd8.)), input(put(Claim_Date,8.),yymmdd8.),'c'));

Number_Years = (intck('Year', lag(input(put(Claim_Date,8.),yymmdd8.)), input(put(Claim_Date,8.),yymmdd8.),'c'));

end;

run;

This is what I get:

 Claim_Date ID Number_Days Number_Weeks Number_Months Number_Years 20141003 100001945 . . . . 20090902 100002160 . . . . 20090902 100002160 . . . . 20090902 100002160 . . . . 20090903 100002160 1 0 0 0 20111124 100002727 . . . . 20111124 100002727 . . . . 20111124 100002727 812 116 26 2

This is what I want:

 Claim_Date ID Number_Days Number_Weeks Number_Months Number_Years 20141003 100001945 . . . . 20090902 100002160 . . . . 20090902 100002160 0 0 0 0 20090902 100002160 0 0 0 0 20090903 100002160 1 0 0 0 20111124 100002727 . . . . 20111124 100002727 0 0 0 0 20111124 100002727 0 0 0 0

Thanks.

Accepted Solutions
Solution
‎09-20-2016 10:42 AM
Super User
Posts: 23,771

## Re: re: calculating number of days between successive dates

First, The point of BY groups is to not have to check for the same ID - that's what it does.

Replace

if id=lag(Id)

with only an Else.

Second - Lag doesn't work the way you think it does, and using it in an IF condition creates issues.

Calculate your lag value every iteration. So after BY

lag_claim= lag(claim_date); add any conversions necessary here as well.

And in in your if first.id set lag_claim to .

Replace lag in functions with lag_claim.

There red are papers galore on how the LAG functions work so I would recommend researching and reading one. Or remember - don't use it conditionally.

All Replies
Solution
‎09-20-2016 10:42 AM
Super User
Posts: 23,771

## Re: re: calculating number of days between successive dates

First, The point of BY groups is to not have to check for the same ID - that's what it does.

Replace

if id=lag(Id)

with only an Else.

Second - Lag doesn't work the way you think it does, and using it in an IF condition creates issues.

Calculate your lag value every iteration. So after BY

lag_claim= lag(claim_date); add any conversions necessary here as well.

And in in your if first.id set lag_claim to .

Replace lag in functions with lag_claim.

There red are papers galore on how the LAG functions work so I would recommend researching and reading one. Or remember - don't use it conditionally.

☑ This topic is solved.