BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Dogo23
Quartz | Level 8

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;

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Tom
Super User Tom
Super User

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

11 REPLIES 11
Tom
Super User Tom
Super User

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

 

Dogo23
Quartz | Level 8

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
Dogo23
Quartz | Level 8

Capture.PNG

Tom
Super User Tom
Super User

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

Dogo23
Quartz | Level 8

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

Dogo23
Quartz | Level 8

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. 

Doc_Duke
Rhodochrosite | Level 12

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

 

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

Dogo23
Quartz | Level 8

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

TomKari
Onyx | Level 15

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

Dogo23
Quartz | Level 8

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.

 

 

SASKiwi
PROC Star

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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

SAS Enterprise Guide vs. SAS Studio

What’s the difference between SAS Enterprise Guide and SAS Studio? How are they similar? Just ask SAS’ Danny Modlin.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 11 replies
  • 7416 views
  • 4 likes
  • 5 in conversation