Desktop productivity for business analysts and programmers

Duplicate Rows When Group By Date

Accepted Solution Solved
Reply
Contributor
Posts: 54
Accepted Solution

Duplicate Rows When Group By Date

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;

Accepted Solutions
Solution
‎03-16-2018 07:28 PM
Super User
Posts: 22,823

Re: Duplicate Rows When Group By Date

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

 

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

View solution in original post


All Replies
Contributor
Posts: 54

Re: Duplicate Rows When Group By Date

Here's the opuput:

 

Capture.PNG

Super User
Posts: 22,823

Re: Duplicate Rows When Group By Date

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.

Solution
‎03-16-2018 07:28 PM
Super User
Posts: 22,823

Re: Duplicate Rows When Group By Date

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

 

group by put(datepart(datevar), yymms.)
Contributor
Posts: 54

Re: Duplicate Rows When Group By Date

Thanks Reeza, this was helpful!

Contributor
Posts: 54

Average number of records by month

[ Edited ]

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;

 

Respected Advisor
Posts: 4,541

Re: Average number of records by month


@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')
Super User
Posts: 22,823

Re: Duplicate Rows When Group By Date

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

Contributor
Posts: 54

Re: Duplicate Rows When Group By Date

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

☑ This topic is solved.

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

Discussion stats
  • 8 replies
  • 232 views
  • 0 likes
  • 3 in conversation