topic Re: How to select the first observation that has a non-missing value among the last N rows? in SAS Programming

How to select the first observation that has a non-missing value among the last N rows?

xyxu — Fri, 05 Aug 2022 18:13:44 GMT

I find operations across rows difficult in SAS. For example, I want to select observations that have a non-missing value, while excluding those for which another observation was already selected among the last N rows. Below is an example for a small N (e.g., N=3), but I want to keep N general.

data have;
 	infile datalines delimiter = "," missover;
	input value;
	datalines;
	.
	.
	1
	.
	.
	.
	.
	1
	.
	1
	.
	.
	.
	1
	1
	.
	.
run;

data want;
 	infile datalines delimiter = "," missover;
	input value select;
	datalines;
	., 0
	., 0
	1, 1
	., 0 
	., 0
	., 0
	., 0
	1, 1
	., 0
	1, 0
	., 0
	., 0
	., 0
	1, 1
	1, 0
	., 0 
	., 0
run;

Re: How to select the first observation that has a non-missing value among the last N rows?

mkeintz — Fri, 05 Aug 2022 18:44:24 GMT

First, thanks for putting both your sample data and expected results in the form of usable data steps. So here is a program actually tested against your data, and identical to your desired results:

%let N=3;
data want (drop=_:);
  set have;

  retain _most_recent_select .; /*Obsnum of most recent select=1*/

  if not missing(value) and _most_recent_select < (_n_-&n) then do;
    select=1;
    _most_recent_select=_n_;
  end;
  else select=0;
run;

Because _most_recent_select is initialized as a missing value, it starts out "less than" all valid numeric values. So the first non-missing VALUE is always selected. After that only obs more than &N after a selected obs is eligible for select=1.

Re: How to select the first observation that has a non-missing value among the last N rows?

Tom — Fri, 05 Aug 2022 19:03:47 GMT

It would help a lot if you explained what the overall goal is because this request seems silly.

Does your data only have 1's and missing? Or could there be other non-missing values? Does it matter what the first non-missing value is?

Why not just use a temporary array to make a rolling list of the previous N values?

%let N=3 ;
data want ;
  set have;
  array previous [&n] _temporary_ ;
  previous[1+mod(_n_,&n)]=value;
  n_previous = n(of previous[*]);
  select = n_previous=1 and not missing(value);
run;

PS: Note there is no need to use such convoluted data steps to share such simple data.

data have;
  input value @@ ;
datalines;
. .
1
. . . .
1
.
1
. . .
1 1
. .
;

data expect ;
  input value select ;
datalines;
. 0
. 0
1 1
. 0
. 0
. 0
. 0
1 1
. 0
1 0
. 0
. 0
. 0
1 1
1 0
. 0
. 0
;