topic Re: Eliminating consecutive values in New SAS User

Eliminating consecutive values

hjjijkkl — Mon, 26 Apr 2021 16:14:09 GMT

From the data below and I am tryign to eliminate those that have two consecutive test above 80. How Do I go about doing My analysis? for example, in the table below, ID R45 will be excluded because it as two test above 80 consecutively (test 90 and test 100). Thanks

ID	Date	test
R45	3/15/19	90
R45	4/15/19	100
R45	5/15/19	70
R45	6/15/19	60
R90	3/15/19	12
R90	4/15/19	120
R90	5/15/19	45
R90	6/15/19	100
R30	3/15/19	72
R30	4/15/19	28
R30	5/15/19	50
R30	6/15/19	220

my output would look like this

ID	Date	test
R90	3/15/19	12
R90	4/15/19	120
R90	5/15/19	45
R90	6/15/19	100
R30	3/15/19	72
R30	4/15/19	28
R30	5/15/19	50
R30	6/15/19	220

Re: Eliminating consecutive values

FreelanceReinh — Mon, 26 Apr 2021 17:19:58 GMT

Hello @hjjijkkl,

If your dataset is grouped by ID and sorted by Date within each ID BY group, you can use a so-called double DOW-loop to eliminate the IDs in question:

data have;
input ID $ Date :mmddyy. test;
format date mmddyy8.;
cards;
R45 3/15/19 90
R45 4/15/19 100
R45 5/15/19 70
R45 6/15/19 60
R90 3/15/19 12
R90 4/15/19 120
R90 5/15/19 45
R90 6/15/19 100
R30 3/15/19 72
R30 4/15/19 28
R30 5/15/19 50
R30 6/15/19 220
;

data want(drop=tbd);
do until(last.id);
  set have;
  by id notsorted;
  if ~first.id & test>80 & lag(test)>80 then tbd=1; /* "to be deleted" */
end;
do until(last.id);
  set have;
  by id notsorted;
  if ~tbd then output;
end;
run;

Re: Eliminating consecutive values

Ksharp — Tue, 27 Apr 2021 13:02:56 GMT

data have;
input ID $ Date :mmddyy. test;
format date mmddyy8.;
cards;
R45 3/15/19 90
R45 4/15/19 100
R45 5/15/19 70
R45 6/15/19 60
R90 3/15/19 12
R90 4/15/19 120
R90 5/15/19 45
R90 6/15/19 100
R30 3/15/19 72
R30 4/15/19 28
R30 5/15/19 50
R30 6/15/19 220
;
data temp;
 set have;
 flag=ifn(test>80,1,0);
run;
proc summary data=temp;
by id flag notsorted;
output out=temp1;
run;
proc sql;
create table want as
select * from have where id not in (select id from temp1 where flag=1 and _freq_>1);
quit;