Rank Top Ten

[Dogo23]

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;

 

[ballardw]

---

@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

[PGStats]

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;