How can I reference a table for a WHERE IN statement?

[BarryP]

This is what I want to accomplish:

Data Table_I_Want;

     set work.Source_Table;

     where MATERIAL_NUMBER in (

'1001'
'1002'
'1005'
'1011'
);
run;


I'd like to be able to accomplish this by referencing a table with a list of material numbers.  It does not need to be a WHERE IN statement.

Thanks!

[ballardw · Accepted solution]

One way:

Data Table_I_Want;
     merge work.Source_Table
           reference_table (in=inreference)
     ;

     by MATERIAL_NUMBER;
     if inreference;
run;

The (in=inreference) creates a SAS temporary variable that is true/false or 1/0 when the current record has a contribution from that data set. This would require both data sets to be sorted by Material_number. Hopefully your reference_table, or what ever the name might be, does not have repeats of any Material_number values.

[BarryP]

Thanks. It's been months since I've touched SAS and wasn't sure how the merge would work. I didn't know if it would only give me 4 rows. Looks like it works as long as my material numbers are unique.

[BarryP]

Ok, I think I have it.  This is how I'm doing it:

Data Material_Num_Table;

     input @1 MATERIAL_NUMBER $54.;

     datalines;

1001

1002
1005

1011
;
run;

data Table_I_Want;

merge Material_Num_Table(in=InMat) Source_Table(;
by MATERIAL_NUMBER ;
if InMat;

run;