Thanks for your response. The code from Ksharp did not quite produce the expected output. Here's what I've done: libname test 'C:\Data'; PROC SORT data=test.Table1; by ID; RUN; PROC TRANSPOSE data=test.Table1 out=test.Table3; var Data; by ID; RUN; PROC SQL; CREATE TABLE test.combine AS SELECT t1.* ,t2.col1 ,t2.col2 ,t2.col3 ,t2.col4 ,t2.col5 FROM test.table2 t1 LEFT JOIN test.table3 t2 on t1.id = t2.id; QUIT; PROC SQL; CREATE TABLE test.final AS SELECT ID ,Case When ((Data1 = col1) or (Data1 = col2) or (Data1 = col3) or (Data1 = col4) or (Data1 = col5)) then '' else Data1 end as Data1 ,Case When ((Data2 = col1) or (Data2 = col2) or (Data2 = col3) or (Data2 = col4) or (Data2 = col5)) then '' else Data2 end as Data2 ,Case When ((Data3 = col1) or (Data3 = col2) or (Data3 = col3) or (Data3 = col4) or (Data3 = col5)) then '' else Data3 end as Data3 ,Case When ((Data4 = col1) or (Data4 = col2) or (Data4 = col3) or (Data4 = col4) or (Data4 = col5)) then '' else Data4 end as Data4 ,Case When ((Data5 = col1) or (Data5 = col2) or (Data5 = col3) or (Data5 = col4) or (Data5 = col5)) then '' else Data5 end as Data5 FROM test.combine; QUIT; Here's the problem: I'm dealing with thousands, even millions, of records. Also, the data variable could go from Data1 to Data25, which means this would be a long program. Is there a way to shorten the code in the last PROC SQL step? Thanks!
... View more
