BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Manhort
Obsidian | Level 7

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;

 

***

1 ACCEPTED SOLUTION

Accepted Solutions
ChrisNZ
Tourmaline | Level 20

> 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;

View solution in original post

9 REPLIES 9
andreas_lds
Jade | Level 19

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

Manhort
Obsidian | Level 7
Apologies, I typed his rows incorrectly.
Kurt_Bremser
Super User

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;
ChrisNZ
Tourmaline | Level 20

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
Obsidian | Level 7
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
Obsidian | Level 7

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
Jade | Level 19

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

ChrisNZ
Tourmaline | Level 20

> 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;

Ready to join fellow brilliant minds for the SAS Hackathon?

Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.

Register today!
How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 9 replies
  • 1411 views
  • 3 likes
  • 4 in conversation