DATA Step, Macro, Functions and more

Keep only successive observations

Accepted Solution Solved
Reply
Contributor
Posts: 65
Accepted Solution

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.


Accepted Solutions
Solution
‎05-11-2017 09:36 AM
Respected Advisor
Posts: 4,927

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

View solution in original post


All Replies
Respected Advisor
Posts: 4,173

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;

Solution
‎05-11-2017 09:36 AM
Respected Advisor
Posts: 4,927

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
Contributor
Posts: 65

Re: Keep only successive observations

[ Edited ]

Yes it works very well, Thank you very much.

☑ This topic is solved.

Need further help from the community? Please ask a new question.

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