Get clients under Contact_History table

[otepalborida]

Hi SAS Community,

Good day!

I am seeking for a help to anyone who knows how I can solve my scenario.

So i have these tables of CI_CONTACT_HISTORY containing all the records of client who are contacted (SMS, TLS, PUSH, EMAIL)

What i am currently doing is, i created a calculated item (FLAG_CALL) to get some clients using their data in CONTACT_HISTORY. Below is the expression on my created calculated item.

CASE
WHEN DATEPART(<</Contact History/CONTACTED_DTTM>>) ^= DATE() - 4 & <</Contact History/CONTACT_HISTORY_STATUS_CD>> = '_40' & <</Contact History/CAMPAIGN_NM>> = 'CAMP984' & <</Contact History/CHANNEL_CD>> = 'SMS'
THEN 'N'
ELSE 'Y'
END

In translation, I want to get all those clients that are not contacted (by SMS) 4 days ago when the campaign (TLS) is executed. If it is not contacted, I'll tagged his FLAG_CALL attribute into "N", meaning the client is not yet eligible for calling since he/she did not receive any SMS D - 4, else it will be tagged as "Y", meaning the client was received an SMS D - 4 and now eligible for calling.

Apparently, while using this expression, it seems that there is something wrong because all the clients are all tagged with "Y" which is not expected.

Hope someone can help.

If the details are not yet okay, I'll explain it further.

Thank you.

Regards,

Otep

[mkeintz]

If you are producing  no "N" results, then SAS is telling you that you have no cases in which all 4 conditions are simultaneously true. Or to be more accurate, there are no such cases that also satisfy any other filters you may have in the rest of your PROC SQL.  There is nothing else that can be deduced from the code fragment you have provided.

Provide the complete SQL code, and the resulting log notes and messages to give us a better idea of whether it is a coding problem, or merely the data revealing the authentic absence of "N" cases.