topic Re: Sub-Query not running , INTNX- is there an issue in how i am using this? in SAS Enterprise Guide

Sub-Query not running , INTNX- is there an issue in how i am using this?

rrmenon — Wed, 24 Apr 2024 00:26:24 GMT

PROC SQL;
SELECT sum(t1.TheoWin) FROM TDCX.vVW_DWH_PTNSlots t1 
JOIN TDCX.vCXComps t2
ON t1.PtnId=t2.ptnid 
WHERE (t1.DateTimeIn BETWEEN t2.compDate AND INTNX('month', t2.compDate, 1))

Re: Sub-Query not running , INTNX- is there an issue in how i am using this?

Patrick — Wed, 24 Apr 2024 00:51:05 GMT

Please be always specific what's not working - like returning an Error (please share log) or just not returning the expected result.

Does DateTimeIn store a SAS Date or DateTime value? Same goes for variable compDate.

Assuming DateTimeIn stores a datetime and compDate a Date value the expression needs to look like:

WHERE (datepart(t1.DateTimeIn) BETWEEN t2.compDate AND INTNX('month', t2.compDate, 1,'b'))

Or if these are both SAS DateTime values then you would need to use a datetime directive for intnx()

WHERE (t1.DateTimeIn BETWEEN t2.compDate AND INTNX('DTmonth', t2.compDate, 1,'b'))

INTNX({interval<multiple><.shift-index>}, start-from, increment<, 'alignment'>)

I would also always add the alignment explicitly. (The default is Beginning which I assume is what you want).

Also be aware that that the BETWEEN operator is inclusive meaning the upper boundary (date start of next month) will be included into the selection.

Re: Sub-Query not running , INTNX- is there an issue in how i am using this?

SASKiwi — Wed, 24 Apr 2024 00:47:30 GMT

If TDCX points to an external database then it's quite possible INTNX won't get translated correctly into that database's SQL dialect. Id suggest trying SQL Passthru if you are reading an external database.