topic Re: Lagging by multiple variables with a reset in All Things Community

Lagging by multiple variables with a reset

lm12abh — Wed, 05 Feb 2020 08:28:12 GMT

Hi,

I want to lag by two groups, id and date. If its the first id then i need to set my lag field to 0. If its then the next date then I want to lag my variable field:

CODE:

RETAIN LAG_FIELD;

	IF FIRST.ID AND FIRST.DATETHEN LAG_FIELD=0.00;
	ELSE if first.DATE then LAG_FIELD=LAG(VARIABLE); /* this is the part causing me errors */
	else LAG_FIELD+0;

EXAMPLE DATA:

ID DATE VARIABLE LAG_FIELD

123 JAN20 12 0

123 FEB20 12 12

321 MAR15 0 0

321 APR15 12 0 /* CURRENT ERROR =12 */

321 MAY15 12 12

Re: Lagging by multiple variables with a reset

ballardw — Wed, 05 Feb 2020 15:06:58 GMT

Some example data to test code against is always a good idea.

Your requirement

If its the first id then i need to set my lag field to 0

would translate to

IF FIRST.ID THEN LAG_FIELD=0.00;

when you include First.date you get each date setting the lag to 0.

You really don't want to call LAG as part of the result of any IF statement as the queue nature of the lag function means that the lag looks at the last time the condition was true, not the previous record.

So something along the lines of

RETAIN LAG_FIELD;

lv = lag(variable);
IF FIRST.ID THEN LAG_FIELD=0.00;
ELSE if first.DATE then LAG_FIELD=lv;

/* after verifying the code is working as intended then
drop lv;
*/

Adding zero to a retained numeric value is the same as doing nothing. So I am not sure why you have the "else lag_field+0;"

Re: Lagging by multiple variables with a reset

lm12abh — Wed, 05 Feb 2020 15:13:18 GMT

Hi, Yes thanks I am running on test data. I had used the exact same method to fix this earlier on today. Thank you very much for the knowledge.