Desktop productivity for business analysts and programmers

Rank Top Ten

Reply
Contributor
Posts: 64

Rank Top Ten

Hi all,

 

I used the following query in the attempt to count top 5 clicks on a certain link by each campaign. The query I used only counted the top 5 records overall, where as I needed it to count the top 5 for each campaign (marketing_history_id. Any suggestions? 

 

The output I get with this code is attached.

 

 

 

proc sql outobs=5;
create table t1 as
select marketing_history_id,
link_name,
count(email_campaign_activity_id) as total
from email_campaign_activity 
and datepart(activity_date) >= '01JAN2018'd
group by 1,2
order by total desc;
quit;

 Capture.JPG

 

Super User
Posts: 13,889

Re: Rank Top Ten


@Dogo23 wrote:

Hi all,

 

I used the following query in the attempt to count top 5 clicks on a certain link by each campaign. The query I used only counted the top 5 records overall, where as I needed it to count the top 5 for each campaign (marketing_history_id. Any suggestions? 

 

The output I get with this code is attached.

 

 

 

proc sql outobs=5;
create table t1 as
select marketing_history_id,
link_name,
count(email_campaign_activity_id) as total
from email_campaign_activity 
and datepart(activity_date) >= '01JAN2018'd
group by 1,2
order by total desc;
quit;

 

 


I suspect that you want:

Group by marketing_history_id

Esteemed Advisor
Posts: 5,616

Re: Rank Top Ten

You will need an extra step:

 

proc sql;
create table t1 as
select 
    marketing_history_id,
    link_name,
    count(email_campaign_activity_id) as total
from email_campaign_activity 
where datepart(activity_date) >= '01JAN2018'd
group by marketing_history_id, link_name
order by marketing_history_id, total desc;
quit;

data want;
do i = 1 by 1 until (last.marketing_history_id);
    set t1; by marketing_history_id;
    if i <= 5 then output;
    end;
drop i;
run;
PG
Ask a Question
Discussion stats
  • 2 replies
  • 90 views
  • 0 likes
  • 3 in conversation