Duplicate Rows When Group By Date

[Dogo23]

Hello,

Does anyone know why I am still getting multiple rows per date when I am grouping by the date? 

proc sql; 
create table t1 as
select 
distinct datepart(campaign_send_datetime) as send_date format = MMYYS.,
count(distinct email_campaign_id) as b
from dmprod.vw_email_campaign_history
group by 1
order by 1;
quit;

[Dogo23]

Here's the opuput:

[Reeza]

SQL doesn't group by formatted values, so it see's each date as a unique date.

So if you want monthly data you need to create a month variable to group by or use a SAS PROC.

[Reeza · Accepted solution]

You could try something like the following, replacing datevar with your own data variable

group by put(datepart(datevar), yymms.)

[Dogo23]

Thanks Reeza, this was helpful!

[Dogo23]

Need some help!

I need to find out by month the average amount of communications a person is getting in a two year period? Any thoughts?

Thanks ahead of time!

proc sql; 
create table t1 as
select 
distinct datepart(campaign_send_datetime) as send_date format = MMYYS.,
count(id_number)/count(history_id) as average
from dm.vw_email_campaign_history
where datepart(campaign_send_datetime) between '01MAR2016'd and '01MAR2018'd
group by 1;
quit;

[Patrick]

---

@Dogo23 wrote:

Need some help!

I need to find out by month the average amount of communications a person is getting in a two year period? Any thoughts?

Thanks ahead of time!

proc sql; 
create table t1 as
select 
distinct datepart(campaign_send_datetime) as send_date format = MMYYS.,
count(id_number)/count(history_id) as average
from dm.vw_email_campaign_history
where datepart(campaign_send_datetime) between '01MAR2016'd and '01MAR2018'd
group by 1;
quit;

---

Post sample data in the form of a SAS data step creating such data.

One of the things which certainly needs change is your group by statement. It should be something like:

group by person_id, intnx('dtmonth',campaign_send_datetime,0,'b')

[Reeza]

@Dogo23 I merged these two threads since they seem identical. Please don't post the same question multiple times. 

[Dogo23]

@Reeza The problems were related but I was looking for a unique answer to the group by problem with multiple datettime records.