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.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.