CODEWITHSUNDEEP
sqlsassas-macro
Jenny Cai
Jenny Cai

Reputation: 25

How can I create a dynamic where clause in a SAS Proc Sql statement?

I want to create the following Proc SQL statement:

%macro query(from_table, return_table, variable);

Proc sql;
    create table &return_table as
        select
        *
    from &from_table
    where &variable contains " 0000 "
    or &variable contains " 3023 "
    or &variable contains " 9999 "
    or &variable contains " 5555 "
    ...
    ;
run;

%mend;

I have a data file of 400 observations of 4 digit codes. How can I iterate through the data file and create the custom where clause. I do not want to type out "or &variable contains " xxxx " " 400 times.

I was thinking of using another macro to create a text variable with a do loop to iterate through the 400 observations, but can't seem to get that to work. I want something like

%macro append_string(table);
    %Let string = ;
    %Do I=1 %To 400;
       %Let string = &string cat("or variable contains" , table[I]);
    %end;
%mend;

What is the right way to approach this? I've tried writing the append_string macro in so many different ways, but nothing works.

Upvotes: 1

Views: 1449

Answers (1)

Joe
Joe

Reputation: 63434

You can use an exists query to do this fairly easily:

data substrings;
input substring $;
datalines;
1234
5678
9012
;;;;
run;

data master_file;
input @1 full_String $15.;
datalines;
1234 58328 2148
9485 12345 9845
9012 19484 1234
5678 56789 9019
9999 99999 9999
;;;;
run;

proc sql;
  select * from master_file M
    where exists (
      select 1 from substrings S
      where findw(M.full_string, trim(S.substring))
      )
    ;
quit;

To replace the word with blank, you can use a join - I use left join here, inner join would only return the matching rows, left join returns all rows with only matching ones edited (and this also shows the matching string):

proc sql;
  select M.full_string, S.substring, tranwrd(M.full_string,trim(S.substring),' ')
  from master_file M
       left join substrings S
       on findw(M.full_String,trim(s.substring))
  ;
quit;

This actually doesn't look for the space delimiter, but your comment suggests that maybe you don't need it to be there. Otherwise you can add it back in via concatenation.

Also, be aware this doesn't necessarily do what you want if more than one string is matched. Change the fourth row from 5678 to 1234 and it will only change the 1234 - it can only do one match per row this way. Doing multiple matches will be a very different operation.

Upvotes: 1

Related Questions