BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
BarryP
Quartz | Level 8

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!


1 ACCEPTED SOLUTION

Accepted Solutions
ballardw
Super User

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.

 

 

View solution in original post

3 REPLIES 3
ballardw
Super User

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
Quartz | Level 8
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
Quartz | Level 8

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;

sas-innovate-2024.png

Don't miss out on SAS Innovate - Register now for the FREE Livestream!

Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 3 replies
  • 519 views
  • 1 like
  • 2 in conversation