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!
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.
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.
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;
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.