proc sql to calculate difference of business days between two dates

[Jordana_Anthem]

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!

[PGStats]

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.

[Jordana_Anthem]

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!

[PGStats · Accepted solution]

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)

[Jordana_Anthem]

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!