topic Re: how to keep first observation to last observation including missings in between? in SAS Programming

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

imsenny — Wed, 01 Jun 2016 04:38:57 GMT

Data sample

ID X (variable)

1 .

1 3

1 .

1 4

1 .

1 2

1 .

1 3

1 .

2 5

2 .

2 3

2 .

2 3

2 .

2 2

2 4

2 .

2 5

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!

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

LinusH — Wed, 01 Jun 2016 05:33:26 GMT

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.

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

Kurt_Bremser — Wed, 01 Jun 2016 05:39:33 GMT

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.

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

Kurt_Bremser — Wed, 01 Jun 2016 05:43:02 GMT

Heh.

@LinusH supplied the logic, I the code 🙂

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

Ksharp — Wed, 01 Jun 2016 08:57:55 GMT

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;

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

FreelanceReinh — Wed, 01 Jun 2016 08:10:11 GMT

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?

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

Ksharp — Wed, 01 Jun 2016 08:55:24 GMT

Yeah. No need _x ,just

not (missing(x)........

I just follow what I am thinking.