topic Re: Keep only successive observations in SAS Programming

Keep only successive observations

soumri — Fri, 28 Apr 2017 10:56:26 GMT

Hello,

If I have a database of the form:

ID RN TN OBS
1 1 1 3.2
1 1 2 3.1
1 1 3 4.0
1 1 4 3.9
1 2 1 3.6
1 2 2 3.9
1 2 3 4.2
1 4 1 3.5
1 4 2 4.4
1 4 3 4.1
2 2 1 5.2
2 2 2 4.8
2 2 3 4.9
3 1 1 5.1
3 1 2 5.0
3 1 3 5.0
3 1 4 5.2
3 2 1 5.6
3 2 2 5.3
3 2 3 5.3
3 2 4 4.9
3 2 5 5.1
3 3 1 5.9
3 3 2 5.9
3 3 3 5.8

(ID = identifier, RN = control rank, TN = test number within the control and OBS = Observation)
If the control row vary from 1 to 4 (in my original database with over 800 miles observations)), how do I keep only individuals with successive control row as 1; 1 and 2; 1, 2 and 3; Or 1, 2, 3 and 4.

If for example an individual has two non-successive control 1 and 3 , because he has missed control 2, it will only be shown in the database by control 1.

This means that if an individual misses a control, the next row of conctrol will no longer be considered in the database.

The result of my example is as follows:

ID RN TN OBS
1 1 1 3.2
1 1 2 3.1
1 1 3 4.0
1 1 4 3.9
1 2 1 3.6
1 2 2 3.9
1 2 3 4.2
3 1 1 5.1
3 1 2 5.0
3 1 3 5.0
3 1 4 5.2
3 2 1 5.6
3 2 2 5.3
3 2 3 5.3
3 2 4 4.9
3 2 5 5.1
3 3 1 5.9
3 3 2 5.9
3 3 3 5.8

thank you for helping me.

Re: Keep only successive observations

Patrick — Fri, 28 Apr 2017 11:16:39 GMT

Below works for you sample data:

data have;
input ID RN TN OBS;
datalines;
1 1 1 3.2
1 1 2 3.1
1 1 3 4.0
1 1 4 3.9
1 2 1 3.6
1 2 2 3.9
1 2 3 4.2
1 4 1 3.5
1 4 2 4.4
1 4 3 4.1
2 2 1 5.2
2 2 2 4.8
2 2 3 4.9
3 1 1 5.1
3 1 2 5.0
3 1 3 5.0
3 1 4 5.2
3 2 1 5.6
3 2 2 5.3
3 2 3 5.3
3 2 4 4.9
3 2 5 5.1
3 3 1 5.9
3 3 2 5.9
3 3 3 5.8
;
run;

data want(drop=_:);
  set have;
  by id rn tn;
  _lag_rn=lag(rn);
  retain _delete_flg;

  if first.id then 
    do;
      if rn=1 then _delete_flg=0;
      else _delete_flg=1;
    end;
  else if first.rn and _delete_flg=0 and rn ne _lag_rn+1 then _delete_flg=1;
  if _delete_flg=0 then output;
run;

Re: Keep only successive observations

PGStats — Fri, 28 Apr 2017 17:27:37 GMT

Keeping it simple with do until():

data have;
input ID RN TN OBS;
datalines;
1 1 1 3.2
1 1 2 3.1
1 1 3 4.0
1 1 4 3.9
1 2 1 3.6
1 2 2 3.9
1 2 3 4.2
1 4 1 3.5
1 4 2 4.4
1 4 3 4.1
2 2 1 5.2
2 2 2 4.8
2 2 3 4.9
3 1 1 5.1
3 1 2 5.0
3 1 3 5.0
3 1 4 5.2
3 2 1 5.6
3 2 2 5.3
3 2 3 5.3
3 2 4 4.9
3 2 5 5.1
3 3 1 5.9
3 3 2 5.9
3 3 3 5.8
;

data want;
current = 0;
do until(last.id);
    set have; by id;
    if RN > current + 1 then skip = 1;
    current = RN;
    if not skip then output;
    end;
drop current skip;
run;

proc print; run;

Re: Keep only successive observations

soumri — Thu, 11 May 2017 13:38:09 GMT

Yes it works very well, Thank you very much.