用于查找值的 proc sql vs data 步骤形成一个包含异常的引用表 [英] proc sql vs data step for looking up values form a reference table that includes exceptions

问题描述

我正在尝试找出特定州特定城市中特定商品的税值.税值在这样的参考表中:

I am trying to find out tax values for a particular good in a particular city in a particular state. Tax values are in a reference table like this:

state    city     Good     tax
---------------------------------
all      all      all      0.07
all      all      chicken  0.04
all      jackson  all      0.01
arizona  all      meat     0.02
arizona  phoenix  meat     0.04
arizona  tucson   meat     0.03
hawaii   all      all      0.08
nevada   reno     cigar    0.11
nevada   vegas    cigar    0.13

现在假设我要为(内华达雷诺雪茄)征税，参考文献中存在完全匹配，因此答案是 0.11.但是，如果我寻找 (nevada reno chicken) 不存在完全匹配，但 (all all all chicken) 可以用作参考，输出将为 0.04.

Now lets say if I am looking for tax for (nevada reno cigar) an exact match exists in the reference so the answer is 0.11. But, if I look for (nevada reno chicken) an exact match does not exist, but (all all chicken) can be used as reference and output will be 0.04.

您能否建议处理这种情况的 PROC SQL 或 match-merge DATA 步骤逻辑?

Can you suggest PROC SQL or match-merge DATA step logic that handles this situation?

推荐答案

这个有点长.在这些情况下，我使用哈希对象.以迭代方式if/then/else"通过查找树尝试查找值.

This is a bit long. I use a hash object in these situations. Iteratively "if/then/else" your way through the look up tree attempting to find a value.

我认为火奴鲁鲁鸡应该在夏威夷全鸡"而不是全鸡"中.

I assume Honolulu chicken should be in "Hawaii all chicken" and not "all all chicken."

我包含了一个用于创建哈希对象的宏.这会使用您的数据、设置的东西来查找和创建并输出带有查找的税款的表.

I included a macro I use for creating the hash object. This uses your data, a set up things to look up and creates and output table with the looked up taxes.

data taxes;
informat state $8.   
         city $12.     
         Good $12.    
         tax best.;
input state $ city $ good $ tax;
datalines;
all      all      all      0.07
all      all      chicken  0.04
all      jackson  all      0.01
arizona  all      meat     0.02
arizona  phoenix  meat     0.04
arizona  tucson   meat     0.03
hawaii   all      all      0.08
hawaii   all      chicken  0.11
nevada   reno     cigar    0.11
nevada   vegas    cigar    0.13
;;;
run;

data to_look_up;
informat lu_state $8.   
         lu_city $12.     
         lu_Good $12.  ;
input lu_state $ lu_city $ lu_good $;
datalines;
nevada reno cigar
nevada reno chicken
hawaii honalulu chicken
texas  dallas steak
;;;
run;

%macro create_hash(name,key,data_vars,dataset);
declare hash &name(dataset:&dataset);
%local i n d;
%let n=%sysfunc(countw(&key));
rc = &name..definekey(
    %do i=1 %to %eval(&n-1);
    "%scan(&key,&i)",
    %end;
    "%scan(&key,&i)"
);
%let n=%sysfunc(countw(&data_vars));
%do i=1 %to &n;
    %let d=%scan(&data_vars,&i);
    rc = &name..definedata("&d");
%end;
rc = &name..definedone();
%mend;

data lookup;
set to_look_up;
    format tax best.
         state $8.   
         city $12.     
         Good $12. ;

    if _N_ = 1 then do;
        %create_hash(scg,state city good, tax,"taxes");
    end;

    state = lu_state;
    city =  lu_city;
    good = lu_good;
    tax = .;

    rc = scg.find();
    if missing(tax) then do;
        /*No exact match - check if state/good combo exists*/   
        city = "all";
        rc = scg.find();
        if missing(tax) then do;
            /*No state/good combo -- check state only taxes*/
            good = "all";
            rc = scg.find();
            if missing(tax) then do;
                /*Check good only*/
                good = lu_good;
                state = "all";
                rc = scg.find();
                if missing(tax) then do;
                    /*Default taxes*/
                    good = "all";
                    rc = scg.find();
                end;
            end;
        end;
    end;
run;

这篇关于用于查找值的 proc sql vs data 步骤形成一个包含异常的引用表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！