Look up missing values in lookup table in SAS

Question

I have a table that looks like this

(MAIN TABLE)
====================================================================================
|| TRANSACTION_ID || TRADE_PARTY_PREFIX || TRADE_PARTY_VALUE || TRADE_PARTY_TYPE
====================================================================================
| 1               | EXAMPLE             | 123456789           | Dealer
--------------------------------------------------------------------------------
| 2               | EXAMPLE             | 123456789           |
--------------------------------------------------------------------------------
| 3               | EXAMPLE_2           | 123456789           | Non-Dealer
--------------------------------------------------------------------------------
| 4               | EXAMPLE             | 987654321           |

I have another table that looks like this

(LOOKUP TABLE)
=================================================================
|| TRADE_PARTY_PREFIX || TRADE_PARTY_VALUE || TRADE_PARTY_TYPE ||
=================================================================
| EXAMPLE             | 123456789           | Dealer
-----------------------------------------------------------------
| EXAMPLE             | 987654321           | Dealer

I want to have a SAS script that replaces all the missing values in the TRADE_PARTY_TYPE by looking up the TRADE_PARTY_PREFIX and TRADE_PARTY_VALUE in the lookup table (but it should not touch the row if that column is already filled in.

Something along the lines of (in pseudo-code):

for row in main_table:
    if row["trade_party_type"] is not null:
        print(row)
    else
        row["trade_party_type"] == lookup_table["trade_party_prefix"]["trade_party_value"]

Not sure how to do this in SAS.

Answer 1

I would use the coalesce function in a PROC SQL step like so:

PROC SQL;
    CREATE TABLE WANT AS 
        SELECT a.transaction_id, a.trade_party_prefix, a.trade_party_value, coalesce(a.trade_party_type,b.trade_party_type) AS trade_party_type
            FROM dset1 a LEFT JOIN dset2 b
                ON a.trade_party_prefix = b.trade_party_prefix AND a.trade_party_value = b.trade_party_value;
QUIT;

The coalesce function evaluates the arguments in order and returns the current value of the first expression that initially does not evaluate to NULL.