topic Re: How can I reference a table for a WHERE IN statement? in SAS Programming

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

BarryP — Thu, 10 Sep 2020 22:21:11 GMT

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!

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

ballardw — Thu, 10 Sep 2020 22:35:14 GMT

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.

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

BarryP — Thu, 10 Sep 2020 22:36:52 GMT

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;

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

BarryP — Thu, 10 Sep 2020 22:38:40 GMT

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.