re: calculating number of days between successive dates

twildone — Mon, 19 Sep 2016 23:43:26 GMT

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.

Re: re: calculating number of days between successive dates

Reeza — Tue, 20 Sep 2016 00:09:10 GMT

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.

topic Re: re: calculating number of days between successive dates in SAS Programming

re: calculating number of days between successive dates

Re: re: calculating number of days between successive dates