Search code examples
sqlsasproc

Select range of variables with PROC SQL in SAS

I want to select rows based on a range of similar variables meet certain conditions:

proc sql;
  create table2 as
  select * from table1 
  where proc1 in ('111', '222', '333') or
        proc2 in ('111', '222', '333') or
        proc3 in ('111', '222', '333');
quit;

Is there a way I can select the variables more efficiently? In SAS' data step, I can use proc1-proc3, but this can't be done in proc SQL.

Solution

  • It is not possible to do exactly that, but there are some options that will probably suite you.

    First write a sql statement that writes these conditions to a macro variable proc_conditions

    proc sql;
  select compbl(name ||' in ('111', '222', '333')')
  into : proc_conditions separated by ' or '
  from sasHelp.vcolumn
  where libName = 'WORK'
    and memName = 'TABLE2'
    and upcase(Name) like 'PROC%'
  ;

    Note that libName's and memName's are always uppercase in the meta data, while field Name's or mixed case (but case insensitive). I added Compbl to reduce multiple blanks to singles, but that is not required.

    You might check the result is proc1 in ('111', '222', '333') or proc2 in ('111', '222', '333') or proc3 in ('111', '222', '333') by writing it to the log.

      %put &proc_conditions;

    Then just use it

      create table2 as
  select * from table1 
  where &proc_conditions;
quit;