don't know if this will help when VEG and FRUIT extend to 7 columns, but have a look data A; input Vegetables $ Fruits $ Result; datalines; Carrot Apple 1 Carrot Pear 2 Tomato Melon 3 Tomato ANY 4 Tomato Banana 5 ANY Apple 6 Potato Banana 7 ; run; * pairs of vegetables and fruits for which I need to retrieve the variable result (even if missing): ; data B; input Vegetables $ Fruits $ ; datalines; Tomato Apple Cuncumber Mango Potato Banana ; run; proc sql _METHOD ; create table a1 as select b.*, a.result from a join b on a.vegetables eq b.vegetables and a.vegetables ne 'ANY' and a.fruits eq 'ANY' /*these are joined just on vegetable */ union select b.*, a.result from a join b on a.fruits eq b.fruits and a.fruits ne 'ANY' and a.vegetables eq 'ANY' /*these are joined just on fruit */ union select b.*, a.result from a join b on a.fruits eq b.fruits and a.vegetables EQ b.vegetables and a.fruits ne 'ANY' and a.vegetables NE 'ANY' /*these are joined on both so must exclude those "ANY" */ ; QUIT ; /*not bring together with original */ PROC SORT DATA= B; BY Vegetables Fruits ; RUN ; PROC SORT DATA=A1; BY Vegetables Fruits ; RUN ; DATA A2 ; MERGE B A1 ; BY Vegetables Fruits ; RUN ;
... View more