topic Re: proc sql to calculate difference of business days between two dates in SAS Programming

proc sql to calculate difference of business days between two dates

Jordana_Anthem — Fri, 31 Jul 2020 17:23:38 GMT

Using proc sql (passing teradata) I need to calculate the number of business days between two dates:

,intck('WEEKDAY', a.OPEN_DATE, b.DSCHRG_DT) as Num_BD_Diff (this did not work)

What is the correct code to use?

Thanks for any advice!

Re: proc sql to calculate difference of business days between two dates

PGStats — Fri, 31 Jul 2020 18:23:08 GMT

Does work. If OPEN_DATE and DSCHRG_DT are dates. If one or the other is a datetime or a character string, it won't work.

Re: proc sql to calculate difference of business days between two dates

Jordana_Anthem — Fri, 31 Jul 2020 18:38:03 GMT

Thank you, I am working with SAS as a pass through for Teradata databases; is there a way to impose a single date format in upstream code to assure this code will work? Thanks so much!

Re: proc sql to calculate difference of business days between two dates

PGStats — Fri, 31 Jul 2020 20:59:33 GMT

I can't help you on the Teradata side of things.

But, on the SAS side,

If both variables are received as datetimes use:

intck('DTWEEKDAY', a.OPEN_DATE, b.DSCHRG_DT)

if a.OPEN_DATE is a date and b.DSCHRG_DT is a datetime, use:

intck('WEEKDAY', a.OPEN_DATE, datepart(b.DSCHRG_DT))

and finally, if one of your variables is a character string, convert it to a SAS date using the PUT() function with the appropriate format for that string.

intck('WEEKDAY', PUT(a.OPEN_DATE, date9.), b.DSCHRG_DT)

Re: proc sql to calculate difference of business days between two dates

Jordana_Anthem — Fri, 31 Jul 2020 21:54:43 GMT

This code will work: ,intck('WEEKDAY', a.OPEN_DATE, b.DSCHRG_DT) as Num_BD_Diff
as long as in an upstream query or subquery the Teradata dates are declared something like:
,cast(b.DSCHRG_DT as date) as discharge_date. Thanks!