How to remove subjects

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 10
Accepted Solution

How to remove subjects

data have;

input subjid $ visit flag$;

cards;

101 1

101 5  Y

101 6

101 7

102 2

102 3

102 6  Y

103 0

103 4 Y

103 5

103 6

;

run;

This flag is produced when the subject has missed 3 consecutive visits. I can delete the observation by giving condition but what I would like to have is delete all the observations related to that subject

and consider those subjects as not complete participation. I tried first. and last. but since few flag falls in the middle, I need help of how to get rid of those subjects with all the observations concerning that subjects. Any help will be highly appreciated!!


Accepted Solutions
Solution
‎07-26-2013 06:41 PM
Super User
Posts: 11,343

Re: How to remove subjects

Your example data, if I understand your needs doesn't have any subjects to keep. Does this do what your are looking for?

data have;
infile cards missover;
input subjid $ visit flag $;
cards;
101 1
101 5  Y
101 6
101 7
102 2
102 3
102 6  Y
103 0
103 4 Y
103 5
103 6
104 1
104 2
104 3
104 4
104 5
104 6
104 7
105 1
105 2
105 3
105 4
105 5
105 6
105 7
;
run;

proc sql;
   create table want as
   select b.*
   from (
         select * from (select distinct subjid from have)
         except
         select * from (select distinct subjid from have where flag='Y')
        ) as a natural join have as b;
quit;

View solution in original post


All Replies
Solution
‎07-26-2013 06:41 PM
Super User
Posts: 11,343

Re: How to remove subjects

Your example data, if I understand your needs doesn't have any subjects to keep. Does this do what your are looking for?

data have;
infile cards missover;
input subjid $ visit flag $;
cards;
101 1
101 5  Y
101 6
101 7
102 2
102 3
102 6  Y
103 0
103 4 Y
103 5
103 6
104 1
104 2
104 3
104 4
104 5
104 6
104 7
105 1
105 2
105 3
105 4
105 5
105 6
105 7
;
run;

proc sql;
   create table want as
   select b.*
   from (
         select * from (select distinct subjid from have)
         except
         select * from (select distinct subjid from have where flag='Y')
        ) as a natural join have as b;
quit;

Super Contributor
Posts: 339

Re: How to remove subjects

soln1 make flag numeric and use aggregate functions to keep the "max" (representing Y) with by-group processing.

proc sql;

     create table want as

     select *

     from have

     group by subjid

     having max((case

               when flag="Y" then 1

               else 0

               end))=0

     ;

quit;

soln2 nested selects - the nested select creates a subset of subjid where at least one occurrence of flag="Y" that is then use on the entire set to only retain subjids not in the list generated by the nested select

proc sql;

     create table want as

     select *

     from have

     where subid not in (select distinct subjid

                                   from have

                                   where flag="Y"

                                   )

     ;

quit;

I'm not at office so I can't test my programs hence the 2 examples. If the list of subjects that are flagged is huge, then the soln2 will feel very slow, in fact slower than a merge between the 2 selects and the first solution will provide better result.

Vincent

Super Contributor
Posts: 297

Re: How to remove subjects

Hi DJ,

The following performs a successful outcome using a SAS datastep.  The retain statement holds a record across multiple observations and by using by group processing we can control when the flag should be held or replaced.  Even if it isn't useful to you in this situation, it is well worth learning more about as it comes in very handy.

DATA HAVE;

INFILE CARDS MISSOVER;

INPUT @1 SUBJID $ @5 VISIT @7 FLAG $;

CARDS;

101 1

101 5 Y

101 6

101 7

102 2

102 3

102 6 Y

103 0

103 4 Y

103 5

103 6

104 1

104 2

;

RUN;

PROC SORT DATA = HAVE;

  BY SUBJID DESCENDING FLAG;

RUN;

DATA WANT;

  SET HAVE;

  BY SUBJID DESCENDING FLAG;

  RETAIN _FLAG;

  IF FIRST.SUBJID THEN _FLAG = FLAG;

    IF _FLAG = "Y" THEN DELETE;

  DROP FLAG;

RUN;

Occasional Contributor
Posts: 10

Re: How to remove subjects

Thank you so much for your quick response.I really appreciate everyone's time to reply me back.Also Scott_mitchell  has given me correct answer. I learned few different ways to get to the solution.Thank you all!!

Respected Advisor
Posts: 3,156

Re: How to remove subjects

Yet another Proc SQL approach:

proc sql;

create table want as

   select * from have

    group by subjid

   having sum(flag='Y')=0;

quit;

For a quickie data step if the data already sorted:

data want1;

  merge have (where=(flag='Y') in=a) have (in=b);

    by subjid;

  if b and not a;

run;

Haikuo

🔒 This topic is solved and locked.

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

Discussion stats
  • 5 replies
  • 303 views
  • 6 likes
  • 5 in conversation