SAS table lookup script

Question

Anybody know how to lookup values from a table in SAS?

For example, if I type in "TBD," the script returns "To be determined," or if I type in "WTF," the script returns 'What the F**k?"

I have been working at this all day -- specifically using the PROC FORMAT and CALL SYMPUT, but for the most basic task, I've come up empty.

Answer 1

The most performing method is using a hash table. It's a data structure to map keys -> values.
Definition of hash table:https://en.wikipedia.org/wiki/Hash_table
Hash tables in SAS: http://support.sas.com/documentation/cdl/en/lrcon/68089/HTML/default/viewer.htm#n1b4cbtmb049xtn1vh9x4waiioz4.htm
For example, if you want to import keys/values from a table lookups

Var1   Var2

TBD    To be determined
WTF    What the f*ck
...

and add the "Var2" value to the table base

Var1   OtherVar
WTF    142454
WTF    3453485
TBD    234
...

you can do this:

data work.base;
    set work.base;
    /* Define the new variable in the base dataset */
    length Var2 $ 30;
    if _N_=1 then do;
        /* Declare the hash object retrieving the data from lookups */
        declare hash h(dataset:"work.lookups");
        /* Define key and value */
        h.DefineKey("Var1");
        h.DefineData("Var2");
        h.DefineDone();
    end;
    /* With .find method it searches for the Var1 actual value in the   *
     * hash table; if found, it assigns the corresponding value to Var2 */
    h.find();
run;