topic Re: Duplicate Rows When Group By Date in SAS Enterprise Guide

Duplicate Rows When Group By Date

Dogo23 — Fri, 16 Mar 2018 22:12:56 GMT

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;

Average number of records by month

Dogo23 — Fri, 16 Mar 2018 20:23:42 GMT

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;

Re: Duplicate Rows When Group By Date

Dogo23 — Fri, 16 Mar 2018 22:15:09 GMT

Here's the opuput:

Re: Duplicate Rows When Group By Date

Reeza — Fri, 16 Mar 2018 22:43:07 GMT

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.

Re: Duplicate Rows When Group By Date

Reeza — Fri, 16 Mar 2018 22:44:32 GMT

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

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

Re: Duplicate Rows When Group By Date

Dogo23 — Fri, 16 Mar 2018 23:29:16 GMT

Thanks Reeza, this was helpful!

Re: Average number of records by month

Patrick — Sat, 17 Mar 2018 01:14:20 GMT

@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')

Re: Duplicate Rows When Group By Date

Reeza — Sat, 17 Mar 2018 01:51:17 GMT

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

Re: Duplicate Rows When Group By Date

Dogo23 — Tue, 20 Mar 2018 21:03:09 GMT

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