topic Re: How to find the greatest value among the last N rows in data step? in SAS Programming

How to find the greatest value among the last N rows in data step?

xyxu — Wed, 03 Aug 2022 22:51:17 GMT

I want to mark a row if it has the greatest non-missing value among the last N rows. Below is an example where N=3:

data have;
 	infile datalines delimiter = "," missover;
	input value;
	datalines;
	.
	.
	1.5
	.
	.
	.
	.
	1.1
	.
	2.0
	.
	.
	.
	2.2
	1.7
	.
	.
	.
run;

data want;
 	infile datalines delimiter = "," missover;
	input value mark count;
	datalines;
	., 0, .
	., 0, .
	1.5, 1, 1
	., 0, 2
	., 0, 3
	., 0, .
	., 0, .
	1.1, 1, 1
	., 0, 2
	2.0, 1, 1
	., 0, 2
	., 0, 3
	., 0, .
	2.2, 1, 1
	1.7, 0, 2
	., 0, 3
	, 0, .
	., 0,.
run;

I attempted to do this with retain command but didn't get what I want:

data get;
set have;
mark = 0;
retain value_old;
if value ne . and value > value_old then do;
mark = 1;
count = 1;
value_old = value;
end;
if lag(count) > 0 then count = lag(count) + 1;
if (value = . or value < value_old) and lag(count) = 3 then do;
count = .;
value_old = .;
end;
run;

Re: How to find the greatest value among the last N rows in data step?

Tom — Wed, 03 Aug 2022 21:01:03 GMT

I don't see how you get that output from your definition. I think you mean something different that what you said.

Here is what it sounds like you asked for:

data have;
	input value @@;
datalines;
. . 1.5 . . . . 1.1 . 2 . . . 2.2 1.7 . .
;

data max_over_last_3;
  set have;
  array last3 [0:2] _temporary_;
  last3[mod(_n_,3)] = value;
  max = max(of last3[*]);
  n = n(of last3[*]);
run;

Result:

Obs    value    max    n

  1      .       .     0
  2      .       .     0
  3     1.5     1.5    1
  4      .      1.5    1
  5      .      1.5    1
  6      .       .     0
  7      .       .     0
  8     1.1     1.1    1
  9      .      1.1    1
 10     2.0     2.0    2
 11      .      2.0    1
 12      .      2.0    1
 13      .       .     0
 14     2.2     2.2    1
 15     1.7     2.2    2
 16      .      2.2    2
 17      .      1.7    1

Re: How to find the greatest value among the last N rows in data step?

mkeintz — Wed, 03 Aug 2022 23:21:09 GMT

I see a logic to generate the PICK dummy, but the logic for COUNT escapes me:

data want;
  set have;
  if value^=. and value>lag(value) and value>lag2(value) then pick=1;
  else pick=0;
run;

Re: How to find the greatest value among the last N rows in data step?

Adriaan_Gouws — Thu, 04 Aug 2022 16:03:52 GMT

Good day

Half the answer was already provided by a previous user, I'm just adding the last bit.

data want_pick;
  set have;
  if value^=. and value>lag(value) and value>lag2(value) then pick=1;
  else pick=0;
run;

%let N = 3;

data want_count (rename=count_clean=count drop=count);
	set want_pick;
	retain count;

	if pick = 1 then
		count=1;
	else if lag(count)>0 then
		count+1;

	if count<=&N. then
		count_clean = count;
	else count_clean=.;
run;