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;
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.
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
;
Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Ready to level-up your skills? Choose your own adventure.