topic Function in PROC SQL calculate working days in SAS Enterprise Guide

Function in PROC SQL calculate working days

CADSP — Wed, 10 Jun 2020 19:55:33 GMT

Hi guys,

I have a table on a server with Holiday data, I would like to get the working days before and after the holiday dates.
The later ones I manage to do with 'case weekdate' for some cases, but my difficulty is on holidays. How can I do this within PROC SQL?

For exemple:

Holiday DscHoliday UPHoliday LWHoliday

24-02-20 Carnaval 26-02-20 21-02-20

25-02-20 Carnaval 26-02-20 21-02-20

Re: Function in PROC SQL calculate working days

SASKiwi — Wed, 10 Jun 2020 20:03:39 GMT

This blog should help. The SAS function discussed INTNX works in SQL.

Re: Function in PROC SQL calculate working days

ChrisHemedinger — Wed, 10 Jun 2020 20:34:30 GMT

This blog about emulating the NETWORKDAYS function from Excel might also help.

Re: Function in PROC SQL calculate working days

CADSP — Thu, 11 Jun 2020 16:38:35 GMT

I tried to use this function, but I couldn't do what I wanted at the end, when I have two holidays in a row I would have to show me the same previous business day, but she can only show me the previous day of the line in question.
(Maybe I can do it, but I'm still new to SAS).

Re: Function in PROC SQL calculate working days

SASKiwi — Thu, 11 Jun 2020 20:08:21 GMT

Please post your input data, your required output data and the code you have tried.

Re: Function in PROC SQL calculate working days

CADSP — Fri, 12 Jun 2020 12:12:49 GMT

Input data:

REQUIRED OUTPUT:

Note: 31/12/2019 and 01/01/2021 are public holidays(Feriado)

My code:

proc sql;
create table work.holidays as
   select Cod, DescFeriado, DtFeriado
       case when weekday(datepart(DtFeriado)) in (2,3,4,5) then 
                     datepart(DtFeriado)+1
		when weekday(datepart(DtFeriado))=6 then datepart(DtFeriado)+3
		when weekday(datepart(DtFeriado))=7 then datepart(DtFeriado)+2
                when weekday(datepart(DtFeriado))=1 then datepart(DtFeriado)+1
        end as UPFeriado FORMAT=date9.,
        case when weekday(datepart(DtFeriado)) in (3,4,5,6,7) then 
                       datepart(DtFeriado)-1
		 when weekday(datepart(DtFeriado))=2 then datepart(DtFeriado)-3
		 when weekday(datepart(DtFeriado))=1 then datepart(DtFeriado)-2
	end as LowerFeriado FORMAT=date9.,
from TableFeriados
where DtFeriao>='01jan2020:0:0:0'dt;