Desktop productivity for business analysts and programmers

INTCK Function Not Working For Me

Accepted Solution Solved
Reply
Contributor
Posts: 64
Accepted Solution

INTCK Function Not Working For Me

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;

 

 


Accepted Solutions
Solution
‎07-09-2018 02:55 PM
Super User
Super User
Posts: 8,275

Re: INTCK Function Not Working For Me

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

 

View solution in original post


All Replies
Solution
‎07-09-2018 02:55 PM
Super User
Super User
Posts: 8,275

Re: INTCK Function Not Working For Me

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

 

Contributor
Posts: 64

Re: INTCK Function Not Working For Me

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)) <= 1Capture.JPG
Contributor
Posts: 64

Re: INTCK Function Not Working For Me

Capture.PNG

Super User
Super User
Posts: 8,275

Re: INTCK Function Not Working For Me

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

Contributor
Posts: 64

Re: INTCK Function Not Working For Me

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

Contributor
Posts: 64

Re: INTCK Function Not Working For Me

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. 

Trusted Advisor
Posts: 2,127

Re: INTCK Function Not Working For Me

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

 

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

Contributor
Posts: 64

Re: INTCK Function Not Working For Me

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

PROC Star
Posts: 1,334

Re: INTCK Function Not Working For Me

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

Contributor
Posts: 64

Re: INTCK Function Not Working For Me

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.

 

 

Super User
Posts: 4,019

Re: INTCK Function Not Working For Me

[ Edited ]

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.

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 11 replies
  • 195 views
  • 3 likes
  • 5 in conversation