topic Re: Rank Top Ten in SAS Enterprise Guide

Rank Top Ten

Dogo23 — Fri, 03 Aug 2018 21:34:28 GMT

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;

Re: Rank Top Ten

ballardw — Fri, 03 Aug 2018 21:44:08 GMT

@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

Re: Rank Top Ten

PGStats — Sat, 04 Aug 2018 03:16:55 GMT

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;