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

I am new to SAS and I know this is a simple question but any help is appreciated. 

 

I have a dataset and I want to filter it based on specific column.

So the code I have is as follow:

 

data filtered;
       set have;
where col1 in ("x", "y", "z");
run;

This way of filtering is logical as long as there is not a large number of parameters to filter on. But lets say instead of x , y, and z I have a list of 20 different parameters. For example, x, y, z, a, b, ...., n. I think the easier way is that I create a table such as bellow: 

data selected_par;
   input parameter_name $1.  ;
   datalines;
x
y
z
n
;

and then I filter my data set based on this table. 

But I don't know how to filter my dataset using this selected_par SAS table. 

Any help is appreciated. 

1 ACCEPTED SOLUTION

Accepted Solutions
LeonidBatkhan
Lapis Lazuli | Level 10

Hi Al_senior,

You can do this:

proc sql;
   create table filtered as
   select * from have
   where col1 in (select parameter_name from selected_par);
quit;

Hope this helps.

View solution in original post

4 REPLIES 4
ballardw
Super User

A concrete example of how you expect things to work might help.

 

You could use Proc SQL and a join. Something like this might work:

Proc sql; 
   create table filtered as
   select a.*
   from have as a inner join selected_par as b
           on a.col1 = b.parameter_name
   ;
quit;
LeonidBatkhan
Lapis Lazuli | Level 10

Hi Al_senior,

You can do this:

proc sql;
   create table filtered as
   select * from have
   where col1 in (select parameter_name from selected_par);
quit;

Hope this helps.

Al_senior
Fluorite | Level 6

Thank you so much for the solution. I know that might be a bit crazy but would please have a look at my latest question and tell me if what I want to do is even feasible? 

 

 

Patrick
Opal | Level 21

Another approach is to create and use a format

proc format;
  value $myfilter
    'x'='1'
    'y'='1'
    'z'='1'
    'n'='1'
    other='0'
  ;
run;

data have;
  do col1='a','x','b','z';
    output;
  end;
run;

data filtered;
  set have;
  if put(col1,$myfilter.) ne '1';
run;

Format can also get created dynamically from tables and one could also use hash table lookups for filtering data based on matches to another table. But these are both things for a bit later in your SAS journey.

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
  • 4 replies
  • 3470 views
  • 3 likes
  • 4 in conversation