Making conditional statements across both rows and columns

[Manhort]

Hi everyone,

***updated question***

If there is a B in var1 occurring on the same date as an A, remove person.

Person 2 is out with 1/25 rows having A and B.

Person 3 is out with 3/20 rows having A and B.

Sorry for making a mess my first try.

***

DATA have;
input id date mmddyy10. var1 $;
format date mmddyy10.;
CARDS;
1 02/20/2016 A
1 02/25/2016 B
2 01/25/2016 A
2 01/25/2016 B
2 01/29/2016 B
2 02/04/2016 A 

3 03/10/2016 A 
3 03/20/2016 A 
3 03/20/2016 B 
;
RUN;

DATA want;
input id date mmddyy10. var1 $;
format date mmddyy10.;
CARDS;
1 02/20/2016 A
1 02/25/2016 B

;
RUN;

***old question***

I'm having trouble making conditional statements across rows and columns. Any help is appreciated.

If there is a B in any var 1-3 occurring on the same date as an A, remove person.

Person 2 is removed because on 1/25 he has a B on his second row in var *3*.

Person 3 is removed because on 3/18 he has both A and B in the vars.

***

***old code***

DATA have;
input id date mmddyy10. var1 $ var2 $ var3 $;
format date mmddyy10.;
CARDS;
1 02/20/2016 B C C
1 02/25/2016 A C C
2 01/25/2016 A B C
2 01/25/2016 A C B
2 01/29/2016 A B C
2 02/04/2016 A B C
3 03/18/2016 A C B
3 03/20/2016 A A C
3 09/06/2016 B A C
;
RUN;

DATA want;
input id date mmddyy10. var1 $ var2 $ var3 $;
format date mmddyy10.;
CARDS;
1 02/20/2016 B A C
1 02/25/2016 A B C
;
RUN;

***

[andreas_lds]

Person 1 has A and B in the same obs, why is this person kept?

[Manhort]

Apologies, I typed his rows incorrectly.

[Kurt_Bremser]

Then see this:

data want;
if 0 then set have; /* defines variables in PDV */
array vars {*} var1-var3;
do until (last.id);
  have_a = 0;
  have_b = 0;
  do until (last.date);
    set have;
    by id date;
    do i = 1 to dim(vars);
      if vars{i} = "A" then have_a = 1;
      if vars{i} = "B" then have_b = 1;
    end;
  end;
end;
if sum(have_a,have_b) < 2 then outflag = 1;
do until (last.id);
  set have;
  by id;
  if outflag then output;
end;
drop have_a have_b i outflag;
run;

[Kurt_Bremser]

Person 1 should also be removed, because in both observations, there is an "A" and a "B", same as here:

3 03/18/2016 A C B

[ChrisNZ]

Your explanations are unclear.

All variables have ABC in their data.

>Person 2 is removed because on 1/25 he has a B on his second row in var 2.

No it doesn't. There's C there (in red below).

>Person 3 is removed because on 3/18 he has both A and B in the vars.

Just like ID=1 ? What's the difference?

Do you mean B in VAR2 and A in VAR1 or VAR3 for the same date?

---

@Manhort wrote:

Hi everyone,

I'm having trouble making conditional statements across rows and columns. Any help is appreciated.

If there is a B in any var 1-3 occurring on the same date as an A, remove person.

Person 2 is removed because on 1/25 he has a B on his second row in var 2.

Person 3 is removed because on 3/18 he has both A and B in the vars.

DATA have;
input id date mmddyy10. var1 $ var2 $ var3 $;
format date mmddyy10.;
CARDS;
1 02/20/2016 B A C
1 02/25/2016 A B C
2 01/25/2016 A B C
2 01/25/2016 A C B
2 01/29/2016 A B C
2 02/04/2016 A B C
3 03/18/2016 A C B
3 03/20/2016 A A C
3 09/06/2016 B A C
;
RUN;

DATA want;
input id date mmddyy10. var1 $ var2 $ var3 $;
format date mmddyy10.;
CARDS;
1 02/20/2016 B A C
1 02/25/2016 A B C
;
RUN;

---

[Manhort]

Sorry for submitting a mess I didn't realize. I simplified the question and the data. I feel like I was trying to do too much at one time anyway.

[Manhort]

Hi everyone,

I'm resubmitting a similar question that I butchered posting earlier.

Here's what I'm trying to do:

If there is a B in var1 occurring on the same date as an A, remove that person.

Person 2 is out with 1/25 rows having A and B.

Person 3 is out with 3/20 rows having A and B.

DATA have;
input id date mmddyy10. var1 $;
format date mmddyy10.;
CARDS;
1 02/20/2016 A
1 02/25/2016 B
2 01/25/2016 A
2 01/25/2016 B
2 01/29/2016 B
2 02/04/2016 A

3 03/10/2016 A
3 03/20/2016 A
3 03/20/2016 B
;
RUN;

DATA want;
input id date mmddyy10. var1 $;
format date mmddyy10.;
CARDS;
1 02/20/2016 A
1 02/25/2016 B

;
RUN;

Any help is appreciated!

[andreas_lds]

I have merged your posts. It is really not necessary to re-post a question.

[ChrisNZ · Accepted solution]

> If there is a B in var1 occurring on the same date as an A, remove that person.

Here's one way, where steps are clearly (hopefully) detailed.

data DELETE ;
 merge HAVE (in=A where=(VAR1='A'))
       HAVE (in=B where=(VAR1='B'))
       ;
 by ID DATE;
 if A & B;    * keep records marking deletion;
run;

data WANT;
 merge HAVE 
       DELETE (in=A keep=ID)
       ;
 by ID;   
 retain DELETE; 
 if first.ID then DELETE=A; * flag IDs marked for deletion;
 if not DELETE;             * keep if ID not marked for deletion; 
run;