BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
lm12abh
Fluorite | Level 6

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

1 ACCEPTED SOLUTION

Accepted Solutions
ballardw
Super User

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

View solution in original post

2 REPLIES 2
ballardw
Super User

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
Fluorite | Level 6

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.

sas-innovate-2024.png

Don't miss out on SAS Innovate - Register now for the FREE Livestream!

Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.

 

Register now!

Discussion stats
  • 2 replies
  • 1470 views
  • 0 likes
  • 2 in conversation