sql - SAS层次结构总和

Question

我有一个带有分层编码表变量的数据集。层级逻辑由LEVEL变量和CODE字符变量的前缀结构决定。有6个(码长从1到6)“聚合”级和终端级(码长10个字符)。

我需要更新节点变量(终端节点的计数 - 聚合级别不计入“更高”聚合，仅计入终端节点) - 所以一个级别的计数总和，例如每个级别 5 的总数计数与每个 6 级相同。我需要计算(总结)“更高”级别节点的权重。

注意:我偏移了输出表的 NODES 和 WEIGHT 变量，这样您就可以更好地理解我在说什么(只需将每个偏移量中的数字相加即可得到相同的值)。

EDIT1:同一代码可以有多个观察结果。一个独特的观察结果是 3 个变量 code + var1 + var2 的组合。

输入表:

ID   level code         var1  var2  nodes  weight  myIndex
1    1     1            .     .     999    999     999
2    2     11           .     .     999    999     999
3    3     111          .     .     999    999     999
4    4     1111         .     .     999    999     999
5    5     11111        .     .     999    999     999
6    6     111111       .     .     999    999     999
7   10     1111119999   01    1     1      0.1     105,5
8   10     1111119999   01    2     1      0.1     109,1
9    6     111112       .     .     999    999     999
10  10     1111120000   01    1     1      0.5      95,0
11   5     11119        .     .     999    999     999
12   6     111190       .     .     999    999     999
13  10     1111901000   01    1     1      0.1      80,7
14  10     1111901000   02    1     1      0.2     105,5

期望的输出表:

ID   level code         var1  var2  nodes    weight              myIndex
1    1     1            .     .     5        1.0                  98,1
2    2     11           .     .     5        1.0                  98,1
3    3     111          .     .     5        1.0                  98,1
4    4     1111         .     .     5        1.0                  98,1
5    5     11111        .     .       3          0.7              98,5
6    6     111111       .     .         2            0.2         107,3
7   10     1111119999   01    1           1               0.1    105,5  
8   10     1111119999   01    2           1               0.1    109,1
9    6     111112       .     .         1            0.5          95,0
10  10     1111120000   01    1           1               0.5     95,0
11   5     11119        .     .       2          0.3              97,2
12   6     111190       .     .         2            0.3          97,2
13  10     1111901000   01    1           1               0.1     80,7
14  10     1111901000   02    1           1               0.2    105,5

这是我想出的代码。它就像我想要的那样工作，但是伙计，它真的很慢。我需要更快的方法，因为这是 Web 服务的一部分，必须根据请求“立即”运行。欢迎任何关于加速代码或任何其他解决方案的建议。

%macro doit;

data temporary;
    set have;
run;

%do i=6 %to 2 %by -1;
    %if &i = 6 %then %let x = 10;
    %else %let x = (&i+1);

    proc sql noprint;
        select count(code)
        into :cc trimmed
        from have
        where level = &i;

        select code
        into :id1 - :id&cc
        from have
        where level = &i;
    quit;

    %do j=1 %to &cc.;

        %let idd = &&id&j;

        proc sql;
        update have t1
            set nodes = (
                       select sum(nodes)
                       from temporary t2
                       where t2.level = &x and t2.code like ("&idd" || "%")),
            set weight = (
                       select sum(weight)
                       from temporary t2
                       where t2.level = &x and t2.code like ("&idd" || "%"))   
            where (t1.level = &i and t1.code like "&idd");
        quit;
    %end;
%end;
%mend doit;

基于@Quentin 解决方案的当前代码:

data have;
input ID level code : $10. nodes weight myIndex;
cards;
1    1  1            .   .    .
2    2  11           .   .    .
3    3  111          .   .    .
4    4  1111         .   .    .
5    5  11111        .   .    .
6    6  111111       .   .    .
7   10  1111110000   1   0.1  105.5
8   10  1111119999   1   0.1  109.1
9    6  111112       .   .    .
10  10  1111129999   1   0.5  95.0
11   5  11119        .   .    .
12   6  111190       .   .    .
13  10  1111900000   1   0.1  80.7
14  10  1111901000   1   0.2  105.5
;

data want (drop=_:);

    *hash table of terminal nodes;
    if (_n_ = 1) then do;
        if (0) then set have (rename=(code=_code weight=_weight));
        declare hash h(dataset:'have(where=(level=10) rename=(code=_code weight=_weight myIndex=_myIndex))');
        declare hiter iter('h');
        h.definekey('ID');
        h.definedata('_code','_weight','_myIndex');
        h.definedone();
    end;

    set have;

    *for each non-terminal node, iterate through;
    *hash table of all terminal nodes, looking for children;
    if level ne 10 then do;
        call missing(weight, nodes, myIndex);

        do _n_ = iter.first() by 0 while (_n_ = 0);
            if trim(code) =: _code then do;  
                weight=sum(weight,_weight);
                nodes=sum(nodes,1);
                myIndex=sum(myIndex,_myIndex*_weight);
            end;
            _n_ = iter.next();
        end;
        myIndex=round(myIndex/weight,.1);
    end;
    output;
run;

Answer 1

这是另一种哈希方法。

这不是使用散列对象进行笛卡尔连接，而是将每个级别 10 节点的节点和权重添加到 6 个适用的父节点中的每一个。这可能比 Quentin 的方法稍微快一些，因为没有冗余的哈希查找。

在构造哈希对象时，它比 Quentin 的方法需要更长的时间，并且使用更多的内存，因为每个终端节点使用不同的键添加 6 次并且经常必须更新现有条目，但之后每个父节点只必须查找自己的个人统计数据，而不是遍历所有终端节点，这样可以节省大量资金。

加权统计数据也是可能的，但您必须更新两个循环，而不仅仅是第二个循环。

data want;
if 0 then set have;
dcl hash h();
h.definekey('code');
h.definedata('nodes','weight','myIndex');
h.definedone();
length t_code $10;
do until(eof);
  set have(where = (level = 10)) end = eof;
  t_nodes = nodes;
  t_weight = weight;
  t_myindex = weight * myIndex;
  do _n_ = 1 to 6;
    t_code = substr(code,1,_n_);
    if h.find(key:t_code) ne 0 then h.add(key:t_code,data:t_nodes,data:t_weight,data:t_myIndex);
    else do;
      nodes + t_nodes;
      weight + t_weight;
      myIndex + t_myIndex;
      h.replace(key:t_code,data:nodes,data:weight,data:MyIndex);
    end;
  end;
end;
do until(eof2);
  set have end = eof2;
  if level ne 10 then do;
    h.find();
    myIndex = round(MyIndex / Weight,0.1);
  end;
  output;
end;
drop t_:;
run;