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.
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.