Lagging by multiple variables with a reset

[lm12abh]

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

123                           FEB20                                     12                                        12

321                           MAR15                                    0                                          0

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

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

321                          MAY15                                      12                                       12

[ballardw · Accepted solution]

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;"

[lm12abh]

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.