Thank you Pradeep. I need the count of weekends for another calculation and in your code provided above is there a way to not enter datalines as i have 300,000 rows with diff dates between two columns and the third column should just give the total business days. Instead of datalines can i reference the data set.
there are actually two data steps used in the code, in second datastep you can set your dataset, ignore the previous data set with datalines.
Good day sharath_rk,
Use the following code to get the number of weekends btween two dates (assuming weekend days are Sunday and Saturday) :
data result (drop=i day_diff);
set have;
day_diff = deliverydate - orderdate + 1;
weekends = 0;
do i = 1 to day_diff;
if weekday(intnx('day',orderdate,i-1)) in (1,7) then weekends = weekends + 1;
end;
run;
Good Day,
Thank you. I used the code and got the below error. Please advise.
File WORK.HAVE.DATA does not exist.
Mr. sharath_rk,
Replace the "have" data set (which is a dummy name) by the data set you are working on to get the required results.
Thank you all. The issue has been resolved using below.
data want;
set have;
weekend_days=0;
if (ORD_ENTRY_DATE > .) and (DLVR_STRT_DATE > .) then
do _n_=OE_DATE to DLVR_STRT_DATE;
if weekday(_n_) in (1, 7) then weekend_days + 1;
end;
run;
Hi,
in the below code is there a way to include the hour/24 to get the difference in hours between OE_DATE to DLVR_STRT_DATE. For example : OE_DATE = 22MAR2014:00:03:29.000000 & DLVR_STRT_DATE = 25MAR2014:13:29:27.000000 means order placed at 12:03 and delivered at 13:29 I need the calc to exclude weekends & reduce the difference of hours in the weekend.
data want;
set have;
weekend_days=0;
if (ORD_ENTRY_DATE > .) and (DLVR_STRT_DATE > .) then
do _n_=OE_DATE to DLVR_STRT_DATE;
if weekday(_n_) in (1, 7) then weekend_days + 1;
end;
run;
Team,
In the below data i need the desired output in hours per below where 29th & 30th are weekends in which only weekend hours are to be excluded. In the first row the calculation should consider starting 31st mar 12:00AM till 31st mar 15:13:46.000000 which would be 15 hours. In the second row the calculation should start from 31st mar 12:00AM till 01APR2014:08:37:39 which would be 24 + 8 = 32 hours. Please advise how do i exclude the weekend hours to get the business hours between
ORD_ENTRY_DATE & DLVR_STRT_DTS.
ORD_ENTRY_DATE | DLVR_STRT_DTS | Desired Output In Hours |
29MAR2014:00:06:17.000000 | 31MAR2014:15:13:46.000000 | 15 |
30MAR2014:00:32:39.000000 | 01APR2014:08:37:39.000000 | 32 |
Try this
Data want ;
Set have ;
If Missing (ORD_ENTRY_DATE)
Or Missing (DLVR_STRT_DTS)
Or DLVR_STRT_DTS < ORD_ENTRY_DATE
Then
Do ;
Output ; /* Missing value for Hours */
Return ;
End ;
Time = 0 ;
Date = ORD_ENTRY_DATE ;
Do until (Date >= DLVR_STRT_DTS) ;
End_Date = MIN(DLVR_STRT_DTS, INTNX ('DTDAY', Date, 1)) ;
If Weekday (Datepart (Date)) in (1, 7) ; /* Weekend */
Else
Time + (End_Date - Date) ; /* Seconds */
Date = End_Date ;
End ;
Hours = INT (Time / 3600) ;
Output ;
Drop Date Time End_Date ;
Run ;
Alert - untested code.
The first part of the code tests for invalid values and returns a missing value .
The next part tests whether the date is a weekend day, and if not calculates the time in seconds from the start to the end (either midnight, or the given order and delivery timestamps).
Richard
Richard,
The code worked Thank you very much.
Regards
Sharath
this link also serve the same purpose
just few modification were needed to get your desired output.
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!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.