SAS EG lagcalculation issue, not calculating las a

问题:

can some one resolve the issue there:

data want;
 set have;
 by mac;

 if first.mac then do; DayDif=0; KmDif=0; end; 
       else do;
          DayDif = Date - lag(Date); /* calculate the difference between two dates */
          KmDiff = Kms - lag(Kms);
      end;
run;

And what I got result is as (0 in first line but . in second):

Mac          Date   Kms           DayDif    KmDif
SP0001      10DEC07 1885462.00000   0        0
SP0001      12DEC07 1885462.00000   .        .
SP0001      30APR09 1885462.00000   505      0
SP0001      15JUL09 1886577.00000   76       1115
SP0001      16JUL09 1887667.00000   1        1090
SP0001      17JUL09 1889181.00000   1        1514
SP0001      17JUL09 1888825.00000   0       -356

.
. 

(here When machine changed, lag is taken as t - (t-2) and not as t - (t-1) ) why???? something wrong in the code ??

   Machine  Date    Kms          DayDif KmDif
   SP0001   01OCT14 2898108.00000   1   1059
   SP0001   02OCT14 2899148.00000   1   1040
   HP0001   03OCT14 2900334.00000   1   1186
   HP0002   17JAN08 926384.00000    0   0
   HP0002   18JAN08 926384.00000    -2450   -1973950
   HP0002   28APR09 1237332.00000   466 310948
   HP0002   29APR09 1238599.00000   1   1267

回答1:

The values for the lag() function are not taken from the previous observation (a common misconception).. They are stored in memory every time the function is executed (see documentation).

In your example, when the machine changes, the lag function is not executed due to the conditional logic (if first.mac then do / else) - so the "t-2" value is simply the result from the previous iteration. Try the following:

data want;
  set have;
  by mac;
  if first.mac then do; 
    DayDif=lag(Date); /* executing the lag for subsequent iteration */
    DayDif=0; 
    KmDif=0; 
  end; 
  else do;
    DayDif = Date - lag(Date); /* calculate the difference between two dates */
    KmDiff = Kms - lag(Kms);
  end;
run;

回答2:

I was able to do it this way...

data want;
 set have;
    by mac;
      RETAIN lag_date lag_kms;
      DROP   lag_date lag_kms; 
       if first.Mac
          then do; 
                  DayDif=0;
                  KmDif=0;
          end; 
          else do;
              DayDif = abs(Date - lag_date);
              KmDif = abs(Kms - lag_kms);
      end;
      lag_date = Date;
      lag_kms = Kms;
run;

Which was giving me the result I wanted..

Mac             Date    Kms        DayDif   KmDif
SP0001  10DEC07 1885462.00000   0         0
SP0001  12DEC07 1885462.00000   2         0
SP0001  30APR09 1885462.00000   505       0
SP0001  15JUL09 1886577.00000   76        1115
SP0001  16JUL09 1887667.00000   1         1090
SP0001  17JUL09 1888825.00000   1         1158
SP0001  17JUL09 1889181.00000   0         356

But Now I need to do one more thing... Total = 1158 + 356 for 17JUL09 and so forth. i.e. add a column in which we will have cummulative sum values grouped by Date.

Any suggestions please?