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;
You could try something like the following, replacing datevar with your own data variable
group by put(datepart(datevar), yymms.)
Here's the opuput:
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.
You could try something like the following, replacing datevar with your own data variable
group by put(datepart(datevar), yymms.)
Thanks Reeza, this was helpful!
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;
@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')
@Dogo23 I merged these two threads since they seem identical. Please don't post the same question multiple times.
@Reeza The problems were related but I was looking for a unique answer to the group by problem with multiple datettime records.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.