issue with calculation between dates using lag

[Suzy_Cat]

Hi there

The numeric value for date '03SEP2019' should be 43711 but the data shows a value of 21837. Thus the calculation of Skipped_days is wrong.  Any one can help with the issue ? 

data test;

set test1;

dateFrom=Service_FromDate;

Lag_Service_toDate=lag(Service_ToDate);

Skipped_days=Service_FromDate-lag(Service_ToDate);

run;

Thanks in advance! 🙂

[unison]

Add a format statement.

format myvar date9.;

replace myvar with your 2 variable names!

-unison

[Suzy_Cat]

Thank you unison!

Thought of the format but did not think it matters. I will try

[Astounding]

First, let's correct two of your assumptions.

The internal SAS date value for September 3, 2019 is 21795 (not 43711).

SAS is not saying that the value of 21837 corresponds to September 3, 2019.  It actually comes from the line above, not shown in your display.  The LAG function is getting the value from the previous observation.  That's why Lag_Service_ToDate is 21795 on the second line of your display.  The LAG function retrieved that value from the first line of your display, corresponding to Service_ToDate of September 3, 2019. 

[Suzy_Cat]

My bad. I got the value from Excel.

I understand how lag works ... I just did not attach the correct part of the result to show the issue.

the issue was if it was the second record for the group, the calculation was wrong but the following records in the same group would be correct.

I will check further tomorrow as the system was down

[Kurt_Bremser · Accepted solution]

You are talking about groups. Your problem might be the way you handle a group change with lag():

Wrong method:

data want;
set have;
by group;
if not first.group then lagdate = lag(date);
run;

Correct method:

data want;
set have;
by group;
lagdate = lag(date)
if first.group then lagdate =.;
run;

For proper function, you must make sure that lag() is called in every iteration of the data step.

[Suzy_Cat]

Hi KurtBremser,

Perfect!  you have directed me to the right direction!!

Now I have got exactly what I wanted! Thank you very much for your great knowledge!

🙂

[ballardw]

---

@Suzy_Cat wrote:

My bad. I got the value from Excel.

I understand how lag works ... I just did not attach the correct part of the result to show the issue.

the issue was if it was the second record for the group, the calculation was wrong but the following records in the same group would be correct.

I will check further tomorrow as the system was down

---

I really don't trust Excel for date manipulation. I received an Excel file very recently that has "dates" of "1/0/1900". It doesn't matter which way you might read that but either the day of the month or the month of the year cannot be 0 in a valid date.