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.
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;
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;
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;
Yes it works very well, Thank you very much.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.