Using proc sql, Where at least 1 column is a value

Question

How do i write in sas:

proc sql; 
create table THIS as 
        select * 
        from MAIN(keep=id col1 -- col34)
    where (AT LEAST ONE OF THE COLUMNS contains 1) ;
    ;

I am having a problem figuring out how to write that last line bc I want to keep all columns so I am not just checking one column i want to check for all of them.

What are the possible values in the columns? Null/0/1? Null/Integer Value? Just 0/1? — Robert Penridge, Oct 31 '17 at 15:44

score 5 · Accepted Answer · answered Oct 31 '17 at 15:45

You will have more flexibility if you use a DATA step instead of PROC SQL since you cannot use variable lists in PROC SQL code.

Assuming all of the variables in your list are numeric you could do something like this.

data this;
   set main ;
   keep id col1 -- col34;
   if whichn(1,of col1 -- col34);
run;

score 4 · Answer 2 · answered Oct 31 '17 at 15:50

Tom is right, the best approach is with a data step. If you are certain you want to do it with SQL though you could do something like this:

proc sql noprint;
  create table THIS as
  select * 
  from MAIN(keep=id col1 -- col34)
  where sum(col1,col2,col3, ... ,col34)
  ;
quit;

Using proc sql, Where at least 1 column is a value

2 Answers2