topic INTCK Function Not Working For Me in SAS Enterprise Guide

INTCK Function Not Working For Me

Dogo23 — Fri, 06 Jul 2018 17:14:21 GMT

Hi all,

I am working with email campaign data and I am trying to narrow down all activities that occurred only within 14 days AFTER the email send date. TO QC my data, I noticed the numbers being to high for a specific campaign, so I've narrowed down to look just at that specific campaign which was sent on 02/01/18

I originally have the INTCK function in the "ON" section for the join, but I moved it to "WHERE" since this sped up the processing time. However, the numbers remain the same and as you can see, I'm still getting date values in the activity_date field that are more than 14 days after the send date (2/1). You can see the output in the attached pic.

Here's the code for reference. Can someone tell me if the see an error in how I am using INTCK?

proc sql;
create table tableau.test as
select a.person_id,
a.execution_id, 
a.campaign_id, 
datepart(a.campaign_send_datetime) as send_date format= mmddyy8.,
datepart(b.activity_date) as activity_date format= mmddyy8.,
	   max(case when activity_type_dim_id = 5 then 1 else 0 end) as opened,
	   max(case when activity_type_dim_id = 2 then 1 else 0 end) as clicked,
	   max(case when activity_type_dim_id in (10,11,14) then 1 else 0 end) as bounced
	from ldmprod.vw_email_campaign_history as a
	left join ldmprod.vw_email_campaign_activity as b
	on a.execution_id = b.execution_id and
	   a.person_id = b.person_id
	where datepart(a.campaign_send_datetime) >= '01JAN2018'd 
	and intck('day', b.activity_date, a.campaign_send_datetime) <= 14
	and upcase(a.receipient_id) not like '%SEED%'
	and upcase(a.receipient_id) not like '%SD%'
	and a.campaign_id like 'M00884004904%'
	and (substr (a.campaign_id_id, 1, 3) = 'M00' 
	or substr (a.campaign_id, 1, 3) = 'O00')
	group by 1,2,3,4
	;
quit;

Re: INTCK Function Not Working For Me

Tom — Fri, 06 Jul 2018 17:21:36 GMT

Do the variables contain date values or datetime values? You are treating them differently in different places.

Here you are treating the value as if it contains datetime values (# of seconds).

datepart(b.activity_date) as activity_date format= mmddyy8.,

But here you are treating them as if they have date values (# of days).

	and intck('day', b.activity_date, a.campaign_send_datetime) <= 14

You could tell INTCK that you are using datetime values.

	and intck('dtday', b.activity_date, a.campaign_send_datetime) <= 14

Or convert them yourself like you did before.

	and intck('day', datepart(b.activity_date), datepart(a.campaign_send_datetime)) <= 14

Re: INTCK Function Not Working For Me

Dogo23 — Fri, 06 Jul 2018 17:33:26 GMT

Hi Tom,

Thanks for catching that. The data did indeed have a datetime value for both tables (e.g. 10NOV2015:15:48:05.000000)

I tried using both of you recomendations, but I still got activity date values well past 2/15 (see attached)

and intck('day', datepart(b.activity_date), datepart(a.campaign_send_datetime)) <= 1

Re: INTCK Function Not Working For Me

Dogo23 — Fri, 06 Jul 2018 17:34:30 GMT

Re: INTCK Function Not Working For Me

Tom — Fri, 06 Jul 2018 17:40:28 GMT

Since you are only printing two digit years are you sure they are in the same century?

Re: INTCK Function Not Working For Me

Doc_Duke — Fri, 06 Jul 2018 18:09:30 GMT

Simplify. Since SAS dates are stored as integers, just subtract the two dates.

(DATEPART(a.campaign_send_datetime) - DATEPART(b.activity_date)) <= 14

Re: INTCK Function Not Working For Me

TomKari — Fri, 06 Jul 2018 18:09:39 GMT

Not a SQL expert, but isn't the fact that you're not grouping by activity_date, but you're not applying a grouping function to it, an issue?

Tom

Re: INTCK Function Not Working For Me

Dogo23 — Fri, 06 Jul 2018 20:07:02 GMT

Going through the data set, i'm only seeing everything in 2018. Elsewhere in the code I've also say "where >= January 1st, 2018".

Re: INTCK Function Not Working For Me

Dogo23 — Fri, 06 Jul 2018 20:08:50 GMT

I tried this too but it seems I'm still getting activity dates after 2/15.

Re: INTCK Function Not Working For Me

Dogo23 — Fri, 06 Jul 2018 20:11:58 GMT

Good catch Tom, that may have been left over from a field I've removed since then. I changed it to group by the first 3 fields. However, it didn't change the outcome.

Re: INTCK Function Not Working For Me

SASKiwi — Fri, 06 Jul 2018 23:37:08 GMT

I think you need to include both activity_date and send_date - the date version not the datetime - in your GROUP BY, to avoid your data being re-merged. Check your SAS log and if you are getting a note about data being re-merged then this needs to be fixed as it could well be interfering with your data selection.

Re: INTCK Function Not Working For Me

Dogo23 — Mon, 09 Jul 2018 18:58:21 GMT

So I think I figured this out. The datetime fields needed to be be switched in the INTCK function. I noticed when I added on INTCK fuction in the select area to visually see the date interval, all of them were 0 or negative numbers which is why ways "where less than 14" did not work. However, I'm sure I still would've needed you help about the datetime vs. date.