BookmarkSubscribeRSS Feed
deleted_user
Not applicable
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 ?*/
2 REPLIES 2
Patrick
Opal | Level 21
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
Not applicable
Thanks Patrick

Never thought it would be so simple. 🙂

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
  • 2 replies
  • 599 views
  • 0 likes
  • 2 in conversation