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

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!

1 ACCEPTED SOLUTION

Accepted Solutions
Ksharp
Super User
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;

View solution in original post

6 REPLIES 6
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

Ksharp
Super User
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;
avepo
Fluorite | Level 6

Thanks ! This was a great help!

PaigeMiller
Diamond | Level 26

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;

 

--
Paige Miller
mkeintz
PROC Star

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;
--------------------------
The hash OUTPUT method will overwrite a SAS data set, but not append. That can be costly. Consider voting for Add a HASH object method which would append a hash object to an existing SAS data set

Would enabling PROC SORT to simultaneously output multiple datasets be useful? Then vote for
Allow PROC SORT to output multiple datasets

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

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
  • 6 replies
  • 736 views
  • 0 likes
  • 6 in conversation