BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Dogo23
Quartz | Level 8

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;
1 ACCEPTED SOLUTION

Accepted Solutions
Reeza
Super User

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

8 REPLIES 8
Dogo23
Quartz | Level 8

Here's the opuput:

 

Capture.PNG

Reeza
Super User

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
Super User

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

 

group by put(datepart(datevar), yymms.)
Dogo23
Quartz | Level 8

Thanks Reeza, this was helpful!

Dogo23
Quartz | Level 8

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
Opal | Level 21

@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
Super User

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

Dogo23
Quartz | Level 8

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

SAS Enterprise Guide vs. SAS Studio

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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