I have 2 tables. In the first there is a list of error id and its description. This is a reference.

id_mistake description m1 a m2 b m3 c

There is also a table with data, where all errors are marked in separate columns as 1 and 0.

 row_num m1 m2 m3 1 1 0 1 2 0 1 1 3 1 1 0

As a result, I want to get a table where I get an error from the 1st table and all the line numbers (+ other data) from the 2nd table.

Like this:

 mistake n m1 1 m1 3 m2 2 m2 3 m3 1 m3 2

    1 answer 1

    This is done using proc transpose .

     proc transpose data=work.fact out=want name=mistake ; by row_num; run;

    In the output table, we want :

     +---------+---------+------+ | row_num | mistake | COL1 | +---------+---------+------+ | 1 | m1 | 1 | | 1 | m2 | 0 | | 1 | m3 | 1 | | 2 | m1 | 0 | | 2 | m2 | 1 | | 2 | m3 | 1 | | 3 | m1 | 1 | | 3 | m2 | 1 | | 3 | m3 | 0 | +---------+---------+------+

    Now it is necessary to cut the lines for which there is no "intersection" of the line and an error.

     data want(drop=errorexist); set want(rename=(col1=errorexist row_num=n)); if errorexist; run;

    At the exit:

     +---+---------+ | n | mistake | +---+---------+ | 1 | m1 | | 1 | m3 | | 2 | m2 | | 2 | m3 | | 3 | m1 | | 3 | m2 | +---+---------+