- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
You could try something like the following, replacing datevar with your own data variable
group by put(datepart(datevar), yymms.)
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Here's the opuput:
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
You could try something like the following, replacing datevar with your own data variable
group by put(datepart(datevar), yymms.)
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
Thanks Reeza, this was helpful!
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
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;
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
@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')
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
@Dogo23 I merged these two threads since they seem identical. Please don't post the same question multiple times.
- Mark as New
- Bookmark
- Subscribe
- Mute
- RSS Feed
- Permalink
- Report Inappropriate Content
@Reeza The problems were related but I was looking for a unique answer to the group by problem with multiple datettime records.