DATE count

[deleted_user]

data dates;
infile datalines
dlm=","
MISSOVER
DSD

;
INPUT
SrNo :$8.
CreatedDate :$20.
ClosedDate :$20.
;
LABEL
SrNo = "SrNo"
CreatedDate = "CreatedDate"
ClosedDate = "ClosedDate"
;


datalines;
1,01OCT10:14:19:00,05OCT10:12:46:00
2,01OCT10:16:07:00,05OCT10:15:30:00
3,01OCT10:08:37:00,01OCT10:10:36:00
24,09OCT10:13:49:00,11OCT10:18:33:00
25,09OCT10:14:00:00,11OCT10:18:59:00
26,09OCT10:14:32:00,09OCT10:15:36:00
27,09OCT10:15:11:00,09OCT10:16:22:00
28,11OCT10:11:18:00,16OCT10:10:48:00
29,11OCT10:13:26:00,12OCT10:08:22:00
30,11OCT10:15:04:00,12OCT10:17:56:00
;
RUN;

data holidays;
infile datalines
dlm=","
MISSOVER
DSD

;
INPUT
holiday :$10.
;
datalines;
02OCT10
03OCT10
13OCT10
;
RUN;

data dates1;
set dates;
FORMAT CreatedDate1 DATETIME18.0 ClosedDate1 DATETIME18.0 ;
CreatedDate1 = input(CreatedDate,DATETIME18.0);
ClosedDate1 = input(ClosedDate,DATETIME18.0);
drop CreatedDate ClosedDate;
run;
data holidays1;
set holidays;
format holiday1 date9.;
holiday1 = input(holiday,DATE9.0);
run;


/*Dear All

I have got two datasets dates1 and holiday1. What i want to do is, In dates1 there are two date columns:
CreatedDate1 and ClosedDate1, I want to count the dates between these two dates matching with the holiday1
column dates of holidays1 dataset. E.G for 1 sr no. , the dates between CreatedDate1 and ClosedDate1
are 01oct2010,02oct2010,03oct2010,04oct2010,05oct2010 and in holiday1 dataset two dates are matching i.e
02oct2010,03oct2010. so i want my output like this:

srno. CreatedDate1 ClosedDate1 count
1 01OCT10:14:19:00 05OCT10:12:46:00 2

So can anyone please help me on this ?*/

[Patrick]

That should do:

proc sql;
select
d.*
, sum(case when h.holiday1 ne . then 1 else 0 end) as N_Holidays
from dates1 d left join holidays1 h
on datepart(d.CreatedDate1) LE holiday1 LE datepart(d.ClosedDate1)
group by SrNo,CreatedDate1,ClosedDate1
;
quit;

[deleted_user]

Thanks Patrick

Never thought it would be so simple. 🙂