topic Re: Flag events occuring on same date in SAS Programming

Flag events occuring on same date

avepo — Wed, 20 Jun 2018 12:57:13 GMT

Hi,

I have a dataset I want to identify if two events occurred on the same date. I am not sure how to even start.

My data look like this

ID date event1 event2

1 4/5/05 1 1

1 4/5/05 0 1

1 4/6/05 0 0

2 5/1/05 1 1

2 5/2/05 0 1

2 5/2/05 1 0

I would like to add a flag like this

ID date event1 event2 Flag

1 4/5/05 1 1 1

1 4/5/05 0 1 1

1 4/6/05 0 0 0

2 5/1/05 1 1 0

2 5/2/05 0 1 1

2 5/2/05 1 0 1

Thanks much!

Re: Flag events occuring on same date

RW9 — Wed, 20 Jun 2018 13:12:39 GMT

Post test data in the form of a datastep!!

As such , this is not tested:

proc sql;
  create table want as
  select a.*,
         case when b.id ne . then 1 else 0 end as flag
  from   have a
  left join have b 
  on     a.id=b.id
  and    a.date=b.date
  and    sum(a.event1,a.event2) ne sum(b.event1,b.event2);
quit;

So basically I am merging have back to itself where id and date match, but event 1 and 2 don't.

Re: Flag events occuring on same date

Ksharp — Wed, 20 Jun 2018 13:18:10 GMT

data have;
input ID  date  $    event1  event2;
cards;
1   4/5/05     1             1
1   4/5/05     0             1
1   4/6/05     0             0
2   5/1/05     1             1
2   5/2/05     0             1 
2   5/2/05     1             0
;
run;
data want;
 do until(last.date);
   set have;
   by id date;
   if event1 then has_event1=1;
   if event2 then has_event2=1;
 end;

  flag=0;

 do until(last.date);
   set have;
   by id date;
   if  has_event1 and has_event2 then flag=1;
   output;
 end;
 drop has_:;
 run;

Re: Flag events occuring on same date

PaigeMiller — Wed, 20 Jun 2018 13:36:35 GMT

Maybe my esteemed colleagues @RW9 and @Ksharp understand the problem and the meaning of event1 and event2 in the original table, but I do not understand what event1 and event2 are, or why I should care if we are truly looking at dates.

But here is my answer, ignoring the variables event1 and event2, and assuming the data is properly sorted

proc freq data=have;
    by id;
    tables date/out=a;
run;
data want;
    merge have a;
    by id date;
    if count>1 then flag=1; else flag=0;
run;

Re: Flag events occuring on same date

mkeintz — Wed, 20 Jun 2018 14:10:02 GMT

Given the data are sorted by id/date, you just need a way to look at the next date (firstobs=2) and at the preceding date (lag(date)). And you need a way to determine whether the record-in-hand is at the begin or end of an id group (SET and BY statements):

data have;
input ID  date  $    event1  event2;
cards;
1   4/5/05     1             1
1   4/5/05     0             1
1   4/6/05     0             0
2   5/1/05     1             1
2   5/2/05     0             1 
2   5/2/05     1             0
;
run;


data want;
  set have (keep=id);
  by id;

  merge have have (firstobs=2 keep=date rename=(date=nxt_date));

  if  (date=nxt_date and last.id=0) or (date=lag(date) and first.id=0)
    then flag=1;
run;

Re: Flag events occuring on same date

novinosrin — Wed, 20 Jun 2018 14:23:55 GMT

data have;
input ID  date  $    event1  event2;
cards;
1   4/5/05     1             1
1   4/5/05     0             1
1   4/6/05     0             0
2   5/1/05     1             1
2   5/2/05     0             1 
2   5/2/05     1             0
;
run;


proc sql;
create table want as
select *,sum(max(event1 , event2))>1 as flag
from have
group by id, date;
quit;

Re: Flag events occuring on same date

avepo — Tue, 26 Jun 2018 16:46:56 GMT

Thanks ! This was a great help!