Select Node

[AlanisN]

Hi all,

I wanted to check with you guys (as I'm not an SAS CI expert) what would be the right interpretation of the Select Node here below.

This is my try: I'm selecting all the customers who were contacted from the communication node COMM265681 (in history) in the last 10, 6 or 3 days.

I also have doubts about the rule at the bottom: Should it be "any value meet these criteria" or "all values meet these criteria"?

Hope you can help me.

Thanks a lot in advance!

[SivaKV]

Hi,

I was used SAS CI a year ago, My understanding on your requirement is, you would like to select the customers who received the specified communication on last 10th day, 6th day and 3rd day. If my understanding is correct , your selection is right or you can also select like today -10 or -6 or -3 as per my knowledge.

Regarding rule, any value meets the criteria means, the customers satisfied all the criteria's specified above will get into the node count.

Regards

Siva

[JamesAnderson]

Hi AlanisN,
The ANY or ALL rule significantly changes the logic of the query generated. It is used when there is a one to many join between the main customer table and the table you are filtering (in your case the Contact History table). A customer might have numerous records in the contact history table, and you are looking for customers who have records with a contact date of last 3, 6 or 10 days. Some customers may have records that fall outside this range (for example 20 days, or 100 days). For example:

Customer 1: Contact Date 5 days ago, and 30 days ago
Customer 2: Contact Date 5 days ago

If your query is "in the last 6 days", and you use the ANY rule, the query will find customers with at least one record within the last 6 days, but may also have records outside of the last 6 days. In this example both Customer 1 and 2 will be selected.
If you query is "in the last 6 days", and you use the ALL rule, the query will find customers who only have records within the last 6 days (i.e. ALL of the contacts happened within the last 6 days). In this example only customer 2 will be selected.

The results of these two rules is very different.

The sql for these two rules is:

ANY:
PROC SQL;
Create table WORK.MA3 as
SELECT DISTINCT table0.CustomerID AS CustomerID LABEL='Customer ID'
FROM
CDM.CI_CONTACT_HISTORY table2 Inner join CDM.CI_CELL_PACKAGE table1
on table2.CELL_PACKAGE_SK = table1.CELL_PACKAGE_SK
Inner join DataMart.CUSTOMER_SUMMARY table3
on table3.CustomerID = table2.CustomerID
Inner join DataMart.CUSTOMER table0
on table0.CustomerID = table3.CustomerID
WHERE
table1.COMMUNICATION_CD = 'COMM265681' AND
(
(
(( table2.CONTACT_DTTM ) >= '29Jul2018:00:00:00'dt AND ( table2.CONTACT_DTTM ) < '30Jul2018:00:00:00'dt) /* 3 days ago */
OR (( table2.CONTACT_DTTM ) >= '26Jul2018:00:00:00'dt AND ( table2.CONTACT_DTTM ) < '27Jul2018:00:00:00'dt) /* 6 days ago */
)
OR (( table2.CONTACT_DTTM ) >= '22Jul2018:00:00:00'dt AND ( table2.CONTACT_DTTM ) < '23Jul2018:00:00:00'dt) /* 10 days ago */
)

;
quit;

ALL:

PROC SQL;
Create table WORK.MA4 as

SELECT
table4.CustomerID AS CustomerID LABEL='idFromSelection1__0 DIR'
FROM

(
SELECT DISTINCT
table0.CustomerID AS CustomerID LABEL='Customer ID',
( count(*) ) AS DII_16 LABEL=''
FROM
CDM.CI_CONTACT_HISTORY table2 Inner join CDM.CI_CELL_PACKAGE table1
on table2.CELL_PACKAGE_SK = table1.CELL_PACKAGE_SK
Inner join DataMart.CUSTOMER_SUMMARY table3
on table3.CustomerID = table2.CustomerID
Inner join DataMart.CUSTOMER table0
on table0.CustomerID = table3.CustomerID
WHERE
table1.COMMUNICATION_CD = 'COMM265681' AND
(
(
( ( table2.CONTACT_DTTM ) >= '29Jul2018:00:00:00'dt AND ( table2.CONTACT_DTTM ) < '30Jul2018:00:00:00'dt) /* 3 days ago */
OR ( ( table2.CONTACT_DTTM ) >= '26Jul2018:00:00:00'dt AND ( table2.CONTACT_DTTM ) < '27Jul2018:00:00:00'dt) /* 6 days ago */
)
OR ( ( table2.CONTACT_DTTM ) >= '22Jul2018:00:00:00'dt AND ( table2.CONTACT_DTTM ) < '23Jul2018:00:00:00'dt) /* 10 days ago */
)
GROUP BY
1
) table4 Inner join (
SELECT
table8.CustomerID AS DIR_1 LABEL='Customer ID',
( count(*) ) AS DII_2 LABEL=''
FROM
DataMart.CUSTOMER table8 Inner join DataMart.CUSTOMER_SUMMARY table9
on table8.CustomerID = table9.CustomerID
Inner join CDM.CI_CONTACT_HISTORY table7
on table9.CustomerID = table7.CustomerID
Inner join CDM.CI_CELL_PACKAGE table6
on table7.CELL_PACKAGE_SK = table6.CELL_PACKAGE_SK
GROUP BY
1
) table5 on (table4.CustomerID = table5.DIR_1 AND table4.DII_16 = table5.DII_2)
;
quit;

Cheers
James

[AlanisN]

Hi James,

Thank you very much for your answer.

Now it's much more clear for me what is the extend of each of these rules.

What was new for me was your explanation about the "Contact Dttm" field (last day of contact) as I thought the way to interpret "today: -10; -6; -3" was "in the last 10 days and 6 days and 3 days" (before I though the ";" meant "or")

I really appreciate the clarification, it made a huge difference!