topic Re: How do i get a variable to evaluate using Lag or Retain in an If-Then statement in SAS Programming

How do i get a variable to evaluate using Lag or Retain in an If-Then statement

icatNYC — Tue, 14 Jan 2020 18:56:00 GMT

Hello SAS Experts,

Possibly a very simple solution to my problem but please advise. I cannot get the Prior_Check variable to evaluate with the proper value. Any help would be appreciated.

Data Sample;
input Date_order Time_order Curr_Value	Level1 Level2;
datalines;
0 1 100 125 110
0 2	103.5	124 109
;
run;

Data Sample_Evaluation;
set Sample;
Length Live_Check Prior_Check Decision $35.;
Live_Check='0000';
Prior_Check='0000';
Decision='0000';
run;

Data Sample_Evaluation;
/*retain Decision;*//* this statement did not work for me*/ 
set Sample_Evaluation;
/*by Date_order Time_order Decision;*/ /* this statement did not work for me*/ 

if Date_order=0 and Time_order=1 then do;
	if Curr_Value>  Level1 then do;
		Live_Check='Active';
		Decision='Active - High';
	end;
	if Curr_Value<  Level2 then do;
		Live_Check='Active';
		Decision='Active-Low';
	end;
end;

if Date_order=0 and Time_order=2 then do;
	if Curr_Value>  Level1 then do;
		Live_Check='Active';
		Prior_Check=lag(Decision);
			if Live_Check='Active' and Prior_Check='0000' then do;
			Decision='Active - High';
			end;
			else if Live_Check='Active' and Prior_Check ne '0000' then do;
			Decision='Already Active - High';
			end;
	end;
	if Curr_Value<  Level2 then do;
		Live_Check='Active';
		Prior_Check=lag(Decision);/*want this to evaluate to 'Active - Low'*/
			if Live_Check='Active' and Prior_Check='0000' then do;
			Decision='Active - Low';
			end;
			else if Live_Check='Active' and Prior_Check ne '0000' then do;
			Decision='Already Active - Low';
			end;
	end;
end;
run;

Re: How do i get a variable to evaluate using Lag or Retain in an If-Then statement

PeterClemmensen — Tue, 14 Jan 2020 19:28:28 GMT

@icatNYC Hi and welcome to the SAS Communtiy! 🙂

If you read the Lag Function Doc carefully you will understand why.

Shortly put, you should not consider the Lag Function a 'Lookback' function. Rather, the LagN Function creates a queue with N elements. Each time the function is executed, the current value of the lagged variable is inserted into the bottom of the queue and the top element of the queue is pushed out of the queue and returned from the function. This is the reason that calling the function conditionally can produce undesirable results.

See this small example to understand the Lag Function

data have;
input id x;
datalines;
1 2
3 4
1 3
2 4
2 5
2 6
;

data want;
   set have;
   lag_x = lag2(x);
run;

/*

Content of queue 

id x  Queue content
1  1  [ 1 | . ] Returned value: .
1  2  [ 2 | 1 ] Returned value: .
1  3  [ 3 | 2 ] Returned value: 1
2  4  [ 4 | 3 ] Returned value: 2
2  5  [ 5 | 4 ] Returned value: 3
2  6  [ 6 | 5 ] Returned value: 4

*/

Re: How do i get a variable to evaluate using Lag or Retain in an If-Then statement

icatNYC — Tue, 14 Jan 2020 19:39:55 GMT

Thanks for the welcome note! I understand the behavior better now, however is there a "lookback" function that I can use to give me the desired results? This code is intended for a long loop and I would like to process everything SAS. I am exploring a VBA option to give me the desired results but I prefer to keep everything in one "system"

Re: How do i get a variable to evaluate using Lag or Retain in an If-Then statement

PeterClemmensen — Tue, 14 Jan 2020 19:53:20 GMT

Since your Lag Function is identical in the two if-blocks, you can simply pre-compute the lagged variable like this

Data Sample_Evaluation;
/*retain Decision;*//* this statement did not work for me*/ 
set Sample_Evaluation;
/*by Date_order Time_order Decision;*/ /* this statement did not work for me*/ 

if Date_order=0 and Time_order=1 then do;
	if Curr_Value>  Level1 then do;
		Live_Check='Active';
		Decision='Active - High';
	end;
	if Curr_Value<  Level2 then do;
		Live_Check='Active';
		Decision='Active-Low';
	end;
end;

Prior_Check=lag(Decision);

if Date_order=0 and Time_order=2 then do;
	if Curr_Value>  Level1 then do;
		Live_Check='Active';
			if Live_Check='Active' and Prior_Check='0000' then do;
			Decision='Active - High';
			end;
			else if Live_Check='Active' and Prior_Check ne '0000' then do;
			Decision='Already Active - High';
			end;
	end;
	if Curr_Value<  Level2 then do;
		Live_Check='Active';
			if Live_Check='Active' and Prior_Check='0000' then do;
			Decision='Active - Low';
			end;
			else if Live_Check='Active' and Prior_Check ne '0000' then do;
			Decision='Already Active - Low';
			end;
	end;
end;
run;

Re: How do i get a variable to evaluate using Lag or Retain in an If-Then statement

icatNYC — Tue, 14 Jan 2020 20:01:10 GMT

Thank you for the tip. The code works as intended now.

Re: How do i get a variable to evaluate using Lag or Retain in an If-Then statement

PeterClemmensen — Tue, 14 Jan 2020 20:04:25 GMT

Anytime, glad to help 🙂