topic Re: Calculation with lag in SAS Programming

Calculation with lag

SASdevAnneMarie — Tue, 16 Feb 2021 09:17:35 GMT

Hello Experts,

My data is :

data donnees;
	input ID X2 X3;
	cards;
1 10 20 
2 38 50
3 45 60
4 70 80
;
run;

I would like to calculate my values with lag from the line _N_-1, but this lags is also calculated. My code is :


data donnees1;
	set donnees;

	/**Calculation of X4 and X5***/
	if ID=1 then
		do;
			X4=.;
			X5=X3;
		end;

	X5_avant=lag(X5);

	if ID=2 then
		do;
			X4=X5_avant+X2;
			X5=X5_avant+X3;
		end;

	X5_avant=lag(X5);

	if ID=3 then
		do;
			X4=X5_avant+X2;
			X5=X5_avant+X3;
		end;

	X5_avant=lag(X5);

	if ID=4 then
		do;
			X4=X5_avant+X2;
			X5=X5_avant+X3;
		end;
run;

I wrote a macro where I recreate the data:


/****Solution***/
%macro values;
	%do i=1 %to 3;

		data donnees;
			set donnees;

			/**Calculation of X4 and X5***/
			if ID=1 then
				do;
					X4=.;
					X5=X3;
				end;

			X5_avant=lag(X5);

			if ID^=1 then
				do;
					X4=X5_avant+X2;
					X5=X5_avant+X3;
				end;

			X5_avant=lag(X5);
		run;

	%end;
%mend;

%values;

Do you know please another more efficient algorithme that takes less time ?

Thank you very much !

Re: Calculation with lag

Kurt_Bremser — Tue, 16 Feb 2021 09:33:23 GMT

Is the result of that macro what you expect?

Re: Calculation with lag

SASdevAnneMarie — Tue, 16 Feb 2021 09:39:49 GMT

Yes, but in my example I have only 4 rows, in my real data I have much more observations. I'm wondering if my code is efficient.

Re: Calculation with lag

Kurt_Bremser — Tue, 16 Feb 2021 10:11:59 GMT

With your example data, this code creates your expected result:

data want;
set donnees;
retain x4 x5 x5_avant; /* x4 and x5 only to keep order of variables */
x4 = x5_avant + x2; /* creates a missing value in 1st observation */
x5 = sum(x5_avant,x3);
output;
x5_avant = sum(x5_avant,x3);
run;

If you wanted x4 to be equal to x2 in the first observation, use a SUM() function to calculate it.

Re: Calculation with lag

SASdevAnneMarie — Tue, 16 Feb 2021 10:37:47 GMT

Thank you Kurt !

As I understood, the "Output" allows to calculated the values of x5_avant on line _N_=3 with the data from _N_=2. The SUM() function is ignorign the missing values ?

Re: Calculation with lag

Kurt_Bremser — Tue, 16 Feb 2021 10:41:49 GMT

SUM() ignores missing values; it will only result in a missing value (and write the corresponding NOTE to the log) when all its arguments are missing.

Re: Calculation with lag

SASdevAnneMarie — Tue, 16 Feb 2021 10:51:14 GMT

Thank you, Kurt !

The x5_avant=20 from line _N_=2 is calculated as sum(x5_avant,x3) from line _N_=1 because of "output" in code? I don't understand how we holded the data from the first line for calculating the values for second line. 🙂

Re: Calculation with lag

Kurt_Bremser — Tue, 16 Feb 2021 10:58:56 GMT

That happens because of the RETAIN statement. It prevents that x5_avant is set to missing at the start of a data step iteration.

And the explicit OUTPUT statement keeps the previous value of x5_avant in the dataset, as it happens before the calculation.

Re: Calculation with lag

SASdevAnneMarie — Tue, 16 Feb 2021 11:00:57 GMT

Thank you, Kurt !

Re: Calculation with lag

SASdevAnneMarie — Tue, 16 Feb 2021 14:34:04 GMT

Kurt,
Is it possible to introduce the condition in your code ? For example,
fo first line (_N_=1) I would like to calculate the values like :
x4 = x5_avant + x2;
x5 = sum(x5_avant,x3);
but for another lines the values are :
x4 = x5_avant + x2/10;
x5 = sum(x5_avant,x2/10); I suppose that we can't, beacause of Retain statement. Thank you for your answer !

Re: Calculation with lag

Kurt_Bremser — Tue, 16 Feb 2021 15:01:45 GMT

Using x5_avant in a simple calculation in observation 1 makes no sense, as it is not set at that time.

But you can expand my code to create non-missing values:

data want;
set donnees;
retain x4 x5 x5_avant;
x4 = sum(x5_avant,ifn(_n_ = 1,x2,x2/10));
x5 = sum(x5_avant,ifn(_n_ = 1,x3,x3/10);
output;
x5_avant = sum(x5_avant,x3);
run;

Re: Calculation with lag

SASdevAnneMarie — Tue, 16 Feb 2021 15:05:29 GMT

Thank you, Kurt !

Re: Calculation with lag

SASdevAnneMarie — Wed, 17 Feb 2021 08:49:01 GMT

Hello Kurt,

Sorry, for insisting 🙂 I adopted the algorithm to my data, but I don't have the right results, I'm wondering if I understand your proposition of code. My code is :

I attached the data Variation3 and file with the right results.

Thank you very much for your help !


data Variation4;
	set Variation3;
	by NO_POLICE;
	retain DeltaEAs EAs EAs_avant;

	if _n_=1 then do;
	DeltaEAs=MT_EVT + 5;
    EAs =max(MT_EA,MT_EVT);
	end;

	if _n_^=1 then do;
	if MT_EVT=0 then DeltaEAs=0;
	if MT_EVT>0 then DeltaEAs=MT_EVT;
	if MT_EVT<0 then DeltaEAs=EAs_avant*(MT_EVT/MT_EA_AVMVT);
    EAs=sum(EAs_avant,DeltaEAs);
	end;

	output;
	EAs_avant = sum(EAs_avant,DeltaEAs);
run;

Re: Calculation with lag

Kurt_Bremser — Wed, 17 Feb 2021 12:41:09 GMT

MT_EVT is missing in the first observation, so DeltaEAs will also be missing, causing EAs_avant to be missing also (all arguments of the SUM function are missing).

Since all subsequent MT_EVT are negative, this calculation is done

DeltaEAs=EAs_avant*(MT_EVT/MT_EA_AVMVT)

and will always result in a missing value for DeltaEAs (as long as EAs_avant is missing), which then causes EAs_avant to stay missing.

All this is clearly reflected in the log (Maxim 2!):

 73         data Variation4;
 74           set Variation3;
 75           by NO_POLICE;
 76           retain DeltaEAs EAs EAs_avant;
 77         
 78           if _n_=1 then do;
 79           DeltaEAs=MT_EVT + 5;
 80             EAs =max(MT_EA,MT_EVT);
 81           end;
 82         
 83           if _n_^=1 then do;
 84           if MT_EVT=0 then DeltaEAs=0;
 85           if MT_EVT>0 then DeltaEAs=MT_EVT;
 86           if MT_EVT<0 then DeltaEAs=EAs_avant*(MT_EVT/MT_EA_AVMVT);
 87             EAs=sum(EAs_avant,DeltaEAs);
 88           end;
 89         
 90           output;
 91           EAs_avant = sum(EAs_avant,DeltaEAs);
 92         run;
 
 NOTE: Missing values were generated as a result of performing an operation on missing values.
       Each place is given by: (Number of times) at (Line):(Column).
       1 bei 79:19    12 bei 86:38   12 bei 87:9    13 bei 91:15   
 NOTE: There were 14 observations read from the data set WORK.VARIATION3.
 NOTE: The data set WORK.VARIATION4 has 14 observations and 9 variables.
 NOTE:  Verwendet wurde: DATA statement - (Gesamtverarbeitungszeit):
       real time           0.02 seconds
       cpu time            0.01 seconds

Re: Calculation with lag

SASdevAnneMarie — Wed, 17 Feb 2021 13:10:36 GMT

Thank you, Kurt,

When I change just for _N_=1 the code to initialize the missing values:

if _n_=1 then do;
	MT_EVT=0;
	MT_EA_AVMVT=0;
	DeltaEAs=0;
    EAs =max(MT_EA,MT_EVT);
	end;

I have the 0 in columns, I can't understand why the value EAs=329577,34745 is not retened in EAs_avant.

Thank you for your help !

Re: Calculation with lag

Kurt_Bremser — Wed, 17 Feb 2021 13:27:04 GMT

In the first observation, DeltaEAs is set to zero, and the SUM function in the last statement of the data step will also set EAs_avant to zero (sum of a missing value and zero).

Then, in all observations where MT_EVT is negative (which it is in your example dataset), this formula is calculated:

DeltaEAs=EAs_avant*(MT_EVT/MT_EA_AVMVT)

Since EAs_avant is zero, the result of this is also zero.

So the SUM of EAs_avant (0) and DeltaEAs (0) will still be zero.

And so the zero perpetuates throughout your dataset.

Re: Calculation with lag

SASdevAnneMarie — Wed, 17 Feb 2021 13:58:02 GMT

Thank you Kurt ! I understood.
Have I nice day !

Marie