topic Re: How to filter a data set based on a table in SAS Programming

How to filter a data set based on a table

Al_senior — Fri, 23 Jul 2021 17:58:07 GMT

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.

Re: How to filter a data set based on a table

ballardw — Fri, 23 Jul 2021 18:14:14 GMT

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;

Re: How to filter a data set based on a table

LeonidBatkhan — Fri, 23 Jul 2021 19:47:30 GMT

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.

Re: How to filter a data set based on a table

Patrick — Sat, 24 Jul 2021 03:28:48 GMT

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.

Re: How to filter a data set based on a table

Al_senior — Sat, 24 Jul 2021 15:47:20 GMT

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?