topic issue with calculation between dates using lag in SAS Enterprise Guide

issue with calculation between dates using lag

Suzy_Cat — Wed, 20 Nov 2019 01:35:55 GMT

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! 🙂

Re: issue with calculation between dates using lag

unison — Wed, 20 Nov 2019 02:00:45 GMT

Add a format statement.

format myvar date9.;

replace myvar with your 2 variable names!

-unison

Re: issue with calculation between dates using lag

Suzy_Cat — Wed, 20 Nov 2019 02:06:59 GMT

Thank you unison!

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

Re: issue with calculation between dates using lag

Astounding — Wed, 20 Nov 2019 03:38:38 GMT

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.

Re: issue with calculation between dates using lag

Suzy_Cat — Wed, 20 Nov 2019 07:07:37 GMT

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

Re: issue with calculation between dates using lag

Kurt_Bremser — Wed, 20 Nov 2019 09:16:06 GMT

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.

Re: issue with calculation between dates using lag

Suzy_Cat — Wed, 20 Nov 2019 09:09:19 GMT

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!

🙂

Re: issue with calculation between dates using lag

ballardw — Wed, 20 Nov 2019 18:18:40 GMT

@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.