How to use a value that comes from a different table?

Question

I created a dataset to calculate a threshold:

Data black;
Set blue;
Lower=p20-2;
Upper=p20+2;
Run;

I would like to use this value the output is something like:

Variables n  lower upper
Val      123  -0.2  0.1

I would like to use upper and lower as thresholds:

Proc sql;
Create table one as
Select * from two
Where (Val < upper and Val > lower)
;quit;

Upper and lower should come from black, while Val should come from two. two looks like

ID Val
42 1471
32 1742
74 4819
...

How can I include the threshold in my dataset in order to filter values from two?

A possible solution could be adding lower value and upper value to two columns, but I do know how to assign the values to these columns.

Stu Sztukowski · Accepted Answer · 2021-03-23T16:52:12.407

If your bounds are static for all rows, you can read them into macro variables and reference them in your SQL query.

data black;
    set blue;
    Lower=p20-2;
    Upper=p20+2;

    /* Save the value of lower/upper to macro variables &lower and &upper */
    call symputx('lower', lower);
    call symputx('upper', upper);
run;

proc sql;
    create table one as
    select * from two
    where &lower. < Val < &upper.
    ;
quit;

If each ID has a specific threshold value, you can use a hash table to look up each value by its key. Hash tables are very efficient in SAS and are a great way of doing lookups of small tables in a big table.

data two;
    set one;

    if(_N_ = 1) then do;
        dcl hash h(dataset: 'blue');
            h.defineKey('id');
            h.defineData('lower', 'upper');
        h.defineDone();

        /* Initialize lower/upper to missing */       
        call missing(lower, upper);
    end;

    /* Take the ID for the current row in the and look it up in hash table. 
       If there is a match, return the value of lower/upper for that ID */
    rc = h.Find();

    /* Only output if the ID is between its threshold */
    if(&lower. < Val < &upper.);
   
    drop rc;
run;

If you prefer to use SQL, you can manipulate the SQL optimizer to force a hash join with the undocumented magic=103 option. This sometimes can be more efficient with joins on smaller tables.

proc sql magic=103;
    create table two as
        select t1.*
        from one as t1
        LEFT JOIN
            black as t2
        ON t1.id = t2.id
        where t2.lower < t1.val < t2.upper
    ;
quit;

How to use a value that comes from a different table?

1 Answers1