How's this? The issue is that you need to search across ItemC1-ItemC10. In this simple approach I transpose T2 so that you get all ItemC1-C10 per ItemNo in a single column, then use that as a nested query.
/* example data */
Data T1;
INFILE DATALINES DSD;
Input ItemNo $ ItemC1 $ ItemC2 $;
Datalines;
"A10" , "MRF" , "Ceat"
"A100" , "MRF" , "Ceat"
"B01" , "IRP" , ""
"B011" , "IRP" , "CRO"
;
Data T2;
INFILE DATALINES DSD;
Input ItemNo $ (ItemC1 - ItemC10) ($);
Datalines;
"A101", "MRF", "", "", "Ceat", "", "", "", "", "", ""
"A100", "MRF", "", "Ceat" , "" , "" , "", "", "", "",""
"B01", "IRP" , "" ,"", "", "CRO","","","","",""
"B0101", "IRP", "CRO" ,"","","","","","","",""
;
/* Transpose columns in T2 */
proc sort data=WORK.T2 out=WORK.SORTTempTableSorted;
by ItemNo;
run;
proc transpose data=WORK.SORTTempTableSorted out=work.Transpose prefix=Column;
var ItemC1 ItemC2 ItemC3 ItemC4 ItemC5 ItemC6 ItemC7 ItemC8 ItemC9 ItemC10;
by ItemNo;
run;
proc sql; select * from transpose where column1 ne '';quit;
/* filter condition on list and itemno */
proc sql; select t2.* from t1,t2
where
t1.itemno = t2.itemno and
t1.itemc1 in (select column1 from transpose where itemno = t1.itemno);quit;
Hope this helps
... View more
