CODEWITHSUNDEEP
sas
Mhort
Mhort

Reputation: 1

SAS - Deleting all rows with ID based on multiple conditions

I have to filter this data based on certain letter codes. If Z occurs within 30 days before A or B on any var1,2,3 then remove all rows with that id. In this example persons 1 and 4 will remain, and 2 and 3 are removed.

I'm having trouble with combining syntax for date comparisons and multiple conditions, and generally how to remove all of a certain ID's rows.

DATA have;
input id date mmddyy10. var1 $ var2 $ var3 $ ;
format date mmddyy10.;
CARDS;
1 09/21/2016 B D D
1 09/28/2016 B D D
1 10/11/2016 Z . .
2 01/14/2016 D Z .
2 01/15/2016 Z D D
2 02/09/2016 A . .
3 09/20/2019 Z D .
3 10/02/2019 A . .
3 10/24/2019 A . .
4 09/01/2017 A . .
4 09/07/2017 Z . .
4 10/10/2017 A . .
;
run;


DATA want;
input id date mmddyy10. var1 $ var2 $ var3 $ ;
format date mmddyy10.;
CARDS;
1 09/21/2016 B D D
1 09/28/2016 B D D
1 10/11/2016 Z . .
4 09/01/2017 A . .
4 09/07/2017 Z . .
4 10/10/2017 A . .
;
run;

Any help is appreciated, thanks.

Upvotes: 0

Views: 553

Answers (1)

PeterClemmensen
PeterClemmensen

Reputation: 4937

Try this

DATA have;
input id date mmddyy10. var1 $ var2 $ var3 $ ;
format date mmddyy10.;
CARDS;
1 09/21/2016 B D D
1 09/28/2016 B D D
1 10/11/2016 Z . .
2 01/14/2016 D Z .
2 01/15/2016 Z D D
2 02/09/2016 A . .
3 09/20/2019 Z D .
3 10/02/2019 A . .
3 10/24/2019 A . .
4 09/01/2017 A . .
4 09/07/2017 Z . .
4 10/10/2017 A . .
;
run;

data want(drop = d1 - d3 c);
   do until (last.id);
      set have;
      by id;
      array v var:;
      array d d1 - d3;
      
      do over v;
         if v = 'Z' then d = date;
         if v in ('A', 'B') and c = . then c = range(date, d);
      end;
   end;

   do until (last.id);
      set have;
      by id;
      if c = 0 | c > 30 then output;
   end;
run;

Result:

id  date        var1  var2  var3 
1   09/21/2016  B     D     D 
1   09/28/2016  B     D     D 
1   10/11/2016  Z             
4   09/01/2017  A             
4   09/07/2017  Z             
4   10/10/2017  A

Upvotes: 2

Related Questions