Data sample
ID X (variable)
1 .
1 .
1 .
1 3
1 .
1 4
1 .
1 2
1 .
1 .
1 3
1 .
1 .
1 .
1 .
1 .
2 5
2 .
2 3
2 .
2 .
2 3
2 .
2 .
2 2
2 4
2 .
2 5
2 .
2 .
2 .
2 .
I would like to save obervations from row4-11 and row17-28... and save it as a different dataset.
Basically.... for ID=1, I want to keep all the observations between the first non-missing obs and the last non-missing obs, and for ID=2 do the same...
Thanks in advance for your help!
data have;
input id x;
cards;
1 .
1 .
1 .
1 3
1 .
1 4
1 .
1 2
1 .
1 .
1 3
1 .
1 .
1 .
1 .
1 .
2 5
2 .
2 3
2 .
2 .
2 3
2 .
2 .
2 2
2 4
2 .
2 5
2 .
2 .
2 .
2 .
;
run;
data int1;
set have;
by id;
retain flag1;
if first.id then flag1 = 0;
if x ne . then flag1 = 1;
obsno = _n_;
run;
proc sort data=int1;
by descending obsno;
run;
data int2;
set int1;
by id notsorted;
retain flag2;
if first.id then flag2 = 0;
if x ne . then flag2 = 1;
run;
proc sort
data=int2 (where=(flag1 = 1 and flag2 = 1))
out=want (drop=flag1 flag2 obsno)
;
by obsno;
run;
If you want to omit the non-missing value rows that start and end your sequence, you need to add another step that eliminates the first. and last. rows for each id.
data have;
input id x;
cards;
1 .
1 .
1 .
1 3
1 .
1 4
1 .
1 2
1 .
1 .
1 3
1 .
1 .
1 .
1 .
1 .
2 5
2 .
2 3
2 .
2 .
2 3
2 .
2 .
2 2
2 4
2 .
2 5
2 .
2 .
2 .
2 .
;
run;
data want;
do until(last.x);
set have;
by id x notsorted;
if first.id then first=1;
if last.id then last=1;
end;
do until(last.x);
set have;
by id x notsorted;
if not (missing(x) and first) and
not (missing(x) and last) then output;
end;
keep id x;
run;
Another DOW loop solution:
data want;
do _n_=1 by 1 until(last.id);
set have;
by id;
if x>. then do;
if start=. then start=_n_;
finish=_n_;
end;
end;
do _n_=1 by 1 until(last.id);
set have;
by id;
if start<=_n_<=finish then output;
end;
drop start finish;
run;
@Ksharp: Why do you need _x?
Yeah. No need _x ,just
not (missing(x)........
I just follow what I am thinking.
Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.
Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.
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.