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;
***
> 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;
Person 1 has A and B in the same obs, why is this person kept?
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;
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
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;
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!
I have merged your posts. It is really not necessary to re-post a question.
> 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;
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.