## how to keep first observation to last observation including missings in between?

Occasional Contributor
Posts: 15

# how to keep first observation to last observation including missings in between?

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...

Super User
Posts: 5,884

## Re: how to keep first observation to last observation including missings in between?

Perhaps not the most efficient way:
First pass - assign row no to a variable (_n_) and delete all Obs with missing until the firs non missing. Use first. logic and a flag to keep track if you encountered the first non missing within the by group.
Second - resort the data set descending based on the previously created row no variable.
Third - repeat the logic from the first pass. Remember to use descending on the BY statement.
Data never sleeps
Super User
Posts: 10,278

## Re: how to keep first observation to last observation including missings in between?

Heh.

@LinusH supplied the logic, I the code

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Super User
Posts: 10,278

## Re: how to keep first observation to last observation including missings in between?

``````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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Super User
Posts: 10,784

## Re: how to keep first observation to last observation including missings in between?

[ Edited ]
``````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;

``````
Posts: 1,256

## Re: how to keep first observation to last observation including missings in between?

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?

Super User
Posts: 10,784

## Re: how to keep first observation to last observation including missings in between?

[ Edited ]
``not (missing(x)........``