Hi Eitan123,
I went and checked my example and made sure it used date columns from 2 different tables - it should be ok.
For the example screen shot you provided you need tell SAS it is a Date Literal (not just a string of characters) by adding d or dt for a datetime after it, for example:
'07Jul2021'd
or
'07Jul2021:10:01:00'dt
FYI when you are in Information Map Studio your Expression in a Data Item must include at least 1 field from a table or it will throw a validation error.
'Actual' is an alias for 'ACT/ACT' so either should work.
Here is an example I used in Information Map Studio:
(I use DATEPART because my columns are datetime).
And then in CI Studio:
and then when I run that node I can see in the log the following PROC SQL query executed:
PROC SQL;
Create table WORK.MA3 as
SELECT
( count(*) ) AS DII_1 LABEL=''
FROM
(
SELECT DISTINCT
table0.IndivID AS INDIVID LABEL='Individual ID'
FROM
DataMart.INDIVIDUAL table0 Inner join DataMart.PORTAL_USERS table1 on table1.IndivID = table0.IndivID
WHERE
( DATDIF(DATEPART( ( table0.CustomerSince ) ),DATEPART( ( table1.AccountCreated ) ),'Actual') ) >= 0.0 AND
( DATDIF(DATEPART( ( table0.CustomerSince ) ),DATEPART( ( table1.AccountCreated ) ),'Actual') ) <= 15000.0
) table2 ;
quit;
HTH,
James
... View more
