Customer Intelligence Studio 6.6: Adding values to calculated date variables in select blocks

[Eitan123]

Hey,

I have a date column called X with which I filter populations in CIS in select blocks.
I also have a column (of date type) called Y.

How can I create the following expression in my select blocks (the numbers are an addition of days)?
X between (Y + 3) and (Y + 6)
meaning getting all matching rows from my DB where X is 3-6 days greater than Y

I already managed to do X = Y, X > Y, X < Y... But when it comes to adding numbers, I don't know how to cast them to date type for it to match to the type of Y

[JamesAnderson]

Hi Eitan123, 

There are 2 SAS functions which can help you: INTNX and DATEDIF, however these are do not generate Pass Through SQL for databases so will result in a increase in campaign runtimes and the amount of data that is passed back and forth between SAS and the database servers. Depending on the size of the campaign population this may range from un-noticeable to disastrous. So use the following instructions with careful attention to assessing the impact for your environment.

Create another calculated item of Numeric Type.

DATDIF(Y,X,'Actual')

This says calculate the number of days between Y and X using an actual calendar (i.e. considering the correct number of days for a given month or year).

Note: If X & Y are datetime in the database then you will need to wrap them in a DATEPART() function.

Then in CI Studio use this calculated item and set the minimum and maximum values to be your thresholds.

You can also achieve something similar with INTNX with the added flexibility of being able to calculate number of days, weeks, months difference.

Regards

James

[Eitan123]

Hey,

Firstly, thank you for your help.

I've made several attemps with DADIF, trying to create another calculate item (in Information maps because that's where my client asks caculated items to be created), but I failed miserably because my expressions don't pass validation.

Also, I checked https://documentation.sas.com/doc/en/vdmmlcdc/8.1/lefunctionsref/p1gz65986b9jqin19zk9xqbi7ns3.htm and I didn't see 'Actual' as a possible inserted parameter, though It does appear in a code example but that's outside of CIS as far as I know. ACT/ACT seems related to this topic.

A bit more info that might help:

X is a field in table A with date format 2013-07-24

Y is a field in table B with date format 2013-07-24 (same format)

Perhaps it is not possible to use two fields from two different tables?

Here are some of my attempted expressions for DATDIFF(Y,X,'Actual'):

DATDIF(DATEPART(<<A.X>>),DATEPART(<<B.Y>>),'ACT/ACT')

DATDIF(DATEPART(<<A.X>>),DATEPART(<<B.Y>>),'Actual')
DATDIF(<<A.X>>,<<B.Y>>,'ACT/ACT')
DATDIF(<<A.X>>,<<B.Y>>,'Actual')

DATDIF(<<'2021-07-07'>>,<<'2021-08-08'>>,'ACT/ACT')
DATDIF(<<'2021-07-07'>>,<<'2021-08-08'>>,'Actual')

[JamesAnderson · Accepted solution]

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

[Eitan123]

Hey,

Then I see no difference between your screenshot and an attemp I've made
DATDIF(DATEPART(<<A.X>>),DATEPART(<<B.Y>>),'Actual')

I'll ask my SAS Support for any malfunctions then...!