topic Re: Remove all rows for one variable based on another condition in SAS Programming

Remove all rows of one observation based on another observation condition

serena13lee — Fri, 14 Aug 2020 17:51:41 GMT

Hi, I have a dataset that contains patients, visit and values. My confusion lies in the fact the data is ordered. I would like to be able to remove all the patients if one of the visits is "unscheduled" or is missing "second". This may have been asked before so feel free to direct me to a posted solution.

Have:

ID visit value

1 first 0

1 second 0

2 first 0

2 unscheduled 0

3 first 0

Want:

ID visit value

1 first 0

1 second 0

My pseudo attempt would be: if missing("second") or contains("unscheduled") delete ID.

Re: Remove all rows for one variable based on another condition

serena13lee — Fri, 14 Aug 2020 17:39:21 GMT

data have;
input ID visit $ value;
datalines;
ID visit value
1 first 0
1 second 0
2 first 0
2 unscheduled 0
3 first 0
;

Re: Remove all rows of one observation based on another observation condition

novinosrin — Fri, 14 Aug 2020 18:03:18 GMT


data have;
input ID   visit   :$12.                 value ;
cards;
1    first                      0

1    second                0

2    first                      0

2    unscheduled       0

3    first                      0
;

proc sql;
create table want as
select *
from have
group by id
having not (max(visit='unscheduled')=1 or max(visit = 'second')=0);
quit;

Re: Remove all rows of one observation based on another observation condition

serena13lee — Fri, 14 Aug 2020 18:48:54 GMT

This is exactly what I was looking for. Thanks!

Re: Remove all rows of one observation based on another observation condition

mkeintz — Fri, 14 Aug 2020 18:53:34 GMT

If the data are sorted by id:

data have;
 input id visit :$11. value;
datalines;
1    first                      0
1    second                0
2    first                      0
2    unscheduled       0
3    first                      0
run;
data want;
  merge have (where=(visit='second') in=second_present)
		have (where=(visit='unscheduled') in=unscheduled_present)
        have;
  by id;
  if second_present=1 and unscheduled_present=0;
run;