🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Quartz | Level 8

## Keep only successive observations

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

1 ACCEPTED SOLUTION

Accepted Solutions
Opal | Level 21

## Re: Keep only successive observations

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;``````
PG
3 REPLIES 3
Opal | Level 21

## Re: Keep only successive observations

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;

``````
Opal | Level 21

## Re: Keep only successive observations

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;``````
PG
Quartz | Level 8

## Re: Keep only successive observations

Yes it works very well, Thank you very much.

Discussion stats
• 3 replies
• 840 views
• 0 likes
• 3 in conversation