topic SAS - Multiple 'Or' filters based on table values in New SAS User

SAS - Multiple 'Or' filters based on table values

dad316 — Mon, 16 Dec 2019 17:15:02 GMT

Hi - How do i use a reference table to create multiple 'or' filters For example, this code i'm looking for would read the below reference table and create a filter with the below criteria. I need an automated/dynamic way to create this filter because the code itself cannot be changed, but new records can be freely inserted and deleted from the reference table. Ideally I would like to use this reference table and store this filter criteria inside macro variables, and then apply these macro variable filters to a different table.

if ((state='NJ' and ID_Number=1) or (state='NY' and ID_Number=4));

Reference Table

Re: SAS - Multiple 'Or' filters based on table values

ballardw — Mon, 16 Dec 2019 17:23:57 GMT

If you already have the reference data in a data set it is likely to be easier to use that to filter the data with Proc Sql. No need for any macro variables for this task unless you like to cause headaches.

A very brief example:

data work.bigdata;
   input state $ Id_number var1;
datalines;
MA 123 45
NJ 1   27
NJ 2   15
NJ 1   18
NH 34  1
NY 4   12
NY 4   3
NY 6   8
;

data work.reftable;
   input state $ Id_number ;
datalines;
NJ  1
NY  4
;

proc sql;
   create table work.want as
   select b.* 
   from work.reftable as a
        left join
        work.bigdata as b
        on a.state=b.state
        and a.id_number =b.id_number
   ;
quit;

The aliases A and B are short hand to reference the data sets and prefix variables to indicate which data set value you want to use.

The Left Join says "find all of the values from A with a match in B" using the criteria in the ON clause, in this case the state and Id_number variables.

Re: SAS - Multiple 'Or' filters based on table values

Reeza — Mon, 16 Dec 2019 17:37:29 GMT

A join is a better filter here than dynamically creating multiple filters. The only exception to this would be if you were using explicit pass through code.

Re: SAS - Multiple 'Or' filters based on table values

dad316 — Mon, 16 Dec 2019 19:51:54 GMT

The issue here is that i don't actually need to filter the entire dataset and only keep records that meet the criteria of the source table. The records in the reference table will need to be flagged so i can subsequently then include them or exclude them. The reference table will just flag records that are special cases.

Sorry i should've specified this.

I also need to apply the filter in a data step here (although i have the flexibility to create macro variables inside proc sql)

Re: SAS - Multiple 'Or' filters based on table values

Reeza — Mon, 16 Dec 2019 19:24:02 GMT

Still better to merge, just merge with a full join instead and then create a flag if the merge is successful.

data want;
merge have filter (in=a);
by state id_number;
if a then flag=1; else flag=0;
run;

Now you have a flag variable that you can use to decide what to do later on.

Re: SAS - Multiple 'Or' filters based on table values

ballardw — Mon, 16 Dec 2019 22:02:33 GMT

@dad316 wrote:

The issue here is that i don't actually need to filter the entire dataset and only keep records that meet the criteria of the source table. The records in the reference table will need to be flagged so i can subsequently then include them or exclude them. The reference table will just flag records that are special cases.

Sorry i should've specified this.

I also need to apply the filter in a data step here (although i have the flexibility to create macro variables inside proc sql)

You posted example code:

if ((state='NJ' and ID_Number=1) or (state='NY' and ID_Number=4));

Does indeed subset your data. An IF without then as you show removes any record that does not have a "true" result for the comparison from the resulting data.

So I think you need to shows us a more complete example of what you are doing.

You can merge data and flag things conditionally:

proc sort data=work.bigdata;
   by state id_number;
run;
proc sort data=work.reftable;
   by state id_number;
run;

data work.combined;
   merge work.bigdata
         work.reftable (in=inref);
   by state id_number;
   if inref then Reftable='Yes';
run;

Not that I would personally be likely to use a variable like Reftable as character. But that shows which ones matched. Likely you could use the INREF (which is true when the current record matches values from the reftable data). The data set option IN= creates a temporary variable with values of 1/0 (true or false) indicating membership of that data set for the current record. Each data set could use the IN= option to create more information with all of the combinations of sets (3 in this case). A record could come from bigdata but not reftable, from both, or from reftable but not bigdata.