在SAS EG4中按区域筛选TOP10国家内的TOP10客户求助

步骤1：修正并准备可运行的示例数据 #

先把你提供的示例数据调整为标准可运行格式：

Data read;
    input region$ Country$ Customername$ Amount;
    datalines;
asia china xx 78
asia pak yy 89
africa tunisia zz 120
asia india aa 200
asia china bb 150
africa egypt cc 180
asia pak dd 95
africa tunisia ee 130
asia india ff 220
asia china gg 170
africa egypt hh 190
asia pak ii 100
africa tunisia jj 140
asia india kk 210
asia china ll 160
africa egypt mm 185
asia pak nn 98
africa tunisia oo 135
asia india pp 205
asia china qq 155
;
run;

步骤2：筛选各区域内的TOP10国家 #

先计算每个区域下各国家的总交易金额，再按金额降序排名取前10：

/* 计算区域-国家维度的总金额 */
Data country_total;
    set read;
    by region Country;
    if first.Country then country_sum = 0;
    country_sum + Amount;
    if last.Country then output;
    keep region Country country_sum;
run;

/* 按区域排名，取TOP10国家 */
proc sql;
    create table top10_countries as
    select region, Country, country_sum,
           rank() over (partition by region order by country_sum desc) as country_rank
    from country_total
    having country_rank <= 10;
quit;

步骤3：在TOP10国家中筛选各区域内的TOP10客户 #

先关联原始数据与TOP10国家列表，再计算客户维度的总金额并排名：

/* 提取TOP10国家的客户交易数据 */
Data target_data;
    merge read(in=a) top10_countries(in=b);
    by region Country;
    if a and b;
run;

/* 计算区域-国家-客户维度的总金额 */
Data customer_total;
    set target_data;
    by region Country Customername;
    if first.Customername then customer_sum = 0;
    customer_sum + Amount;
    if last.Customername then output;
    keep region Country Customername customer_sum;
run;

/* 按区域-国家排名，取TOP10客户 */
proc sql;
    create table top10_customers_final as
    select region, Country, Customername, customer_sum,
           rank() over (partition by region, Country order by customer_sum desc) as customer_rank
    from customer_total
    having customer_rank <= 10;
quit;

补充说明 #

• 若数据量较大，可改用proc rank提升效率，示例如下：

  /* 用proc rank给国家排名 */
  proc rank data=country_total out=country_ranked descending ties=low;
      by region;
      var country_sum;
      ranks country_rank;
  run;
  Data top10_countries;
      set country_ranked;
      where country_rank < 10; /* proc rank从0开始计数 */
  run;
  

• 处理并列排名时，可通过ties=low/ties=high参数控制排名规则，根据实际需求调整。

内容的提问来源于stack exchange，提问作者Shafat Ahmad