date

[Smitha9]

I have a data

ID       wakeuptime                    joindate                          enddate                                      set

a    04/11/2008 07:12:00           04/11/2008 05:12:00      04/24/2008 06:10:00                   1

a    04/23/2008 09:12:00          05/06/2008 06:07:00       04/24/2008 06:10:00                  2

a    05/06/2008 09:10:00          05/06/2008 06:07:00       05/07/2008 07:10:00                  2

I want the data, which wakeuptime has the set as join date.

ID             wakeuptime                                   set 

 a     04/11/2008 07:12:00                               1    

a      04/23/2008 09:12:00                               1

a      05/06/2008 09:10:00                               2

thank you.

[Reeza]

proc sql;
create table want as
select t1.ID, t1.wakeuptime, t2.set
from have as t1
left join have as t2
on t1.id = t2.id
and t1.wakeuptime between t2.joindate and t2.enddate;
quit;

I'm not sure this works, especially for your last case. Does enddate factor in anywhere?

Can you explain your logic some more?

[andreas_lds]

---

@Smitha9 wrote:

I have a data

ID       wakeuptime                    joindate                          enddate                                      set

a    04/11/2008 07:12:00           04/11/2008 05:12:00      04/24/2008 06:10:00                   1

a    04/23/2008 09:12:00          05/06/2008 06:07:00       04/24/2008 06:10:00                  2

a    05/06/2008 09:10:00          05/06/2008 06:07:00       05/07/2008 07:10:00                  2

I want the data, which wakeuptime has the set as join date.

ID             wakeuptime                                   set 

 a     04/11/2008 07:12:00                               1    

a      04/23/2008 09:12:00                               1

a      05/06/2008 09:10:00                               2

thank you.

---

Please post data in usable form and explain in detail the logic to be applied.

[Kurt_Bremser]

---

@Smitha9 wrote:

I have a data

ID       wakeuptime                    joindate                          enddate                                      set

a    04/11/2008 07:12:00           04/11/2008 05:12:00      04/24/2008 06:10:00                   1

a    04/23/2008 09:12:00          05/06/2008 06:07:00       04/24/2008 06:10:00                  2

a    05/06/2008 09:10:00          05/06/2008 06:07:00       05/07/2008 07:10:00                  2

I want the data, which wakeuptime has the set as join date.

ID             wakeuptime                                   set 

 a     04/11/2008 07:12:00                               1    

a      04/23/2008 09:12:00                               1

a      05/06/2008 09:10:00                               2

thank you.

---

Please post example data in a data step, like I do here:

data have;
input ID $ wudate :mmddyy10. wutime :time8. jdate :mmddyy10. jtime :time8.;
wakeuptime = dhms(wudate,0,0,wutime);
joindate = dhms(jdate,0,0,jtime);
format wakeuptime joindate nldatms19.;
drop wudate wutime jdate jtime;
datalines;
a 04/11/2008 07:12:00 04/11/2008 05:12:00
a 04/23/2008 09:12:00 05/06/2008 06:07:00
a 05/06/2008 09:10:00 05/06/2008 06:07:00
;

so we know exactly what we're dealing with. It's not rocket science, but a very useful SAS skill, it won't make your head explode, and is considered a courtesy by all of us.

Based on this, I ran

data want;
set have;
by joindate;
if first.joindate
then set = 1;
else set + 1;
drop joindate;
run;

proc print data=want noobs;
run;

and got this, which matches your expected result:

ID	wakeuptime		set
a	11.04.2008 07:12:00	1
a	23.04.2008 09:12:00	1
a	06.05.2008 09:10:00	2

If that does not do what you really want, please explain your rule in depth.