topic Re: where statement: multi variables have the same value in SAS Programming

where statement: multi variables have the same value

sasecn — Tue, 23 Jan 2018 00:51:38 GMT

I have a question about how to use the WHERE statement in data step and in proc sql. My data is like below:

 data old;
input flag1 flag2 flag3 flag4 flag5;
datalines;
0 1 0 1 0
1 1 1 1 1
1 1 1 1 0
0 0 0 0 0
;

I want to keep the subset that all flag variables have value of 1. In this case, the result should only contain the second row. I do have more flag variables than just 5. I don't want to write "where flag1=1 and flag2=1 and ....". How should I do that? Just want to fill the where statement below:

proc sql;
create table new as
select *,
from old
where ??????;
quit;

data new;
  set old;
  where ??????????;
  run;

Many thanks!

Re: where statement: multi variables have the same value

Reeza — Tue, 23 Jan 2018 00:57:35 GMT

Unfortunately you can't use variable lists inside a WHERE statement, but you can with an IF statement. So it depends on whether you're looking for efficiency in terms of your time or processing time.

The trick is to add the variables together, and if they add to the expected number then you can easily filter the observations needed.

If sum(of flag1-flag5) = 5;

If you don't know how many variables you have or need to account for missing values the following is also an option:

if sum(of flag1-flag5) = n(of flag1-flag5);

@sasecn wrote:

I have a question about how to use the WHERE statement in data step and in proc sql. My data is like below:
 data old;
input flag1 flag2 flag3 flag4 flag5;
datalines;
0 1 0 1 0
1 1 1 1 1
1 1 1 1 0
0 0 0 0 0
;
I want to keep the subset that all flag variables have value of 1. In this case, the result should only contain the second row. I do have more flag variables than just 5. I don't want to write "where flag1=1 and flag2=1 and ....". How should I do that? Just want to fill the where statement below:
proc sql;
create table new as
select *,
from old
where ??????;
quit;

data new;
  set old;
  where ??????????;
  run;
Many thanks!

Re: where statement: multi variables have the same value

sasecn — Tue, 23 Jan 2018 01:08:44 GMT

Thanks for your reply! The trick works for my test sample. But my exact data will have millions of observations and more than 30 flag variables. Does the IF trick will increase a lot of my processing time? I am not sure.

Re: where statement: multi variables have the same value

novinosrin — Tue, 23 Jan 2018 01:21:05 GMT

data old;
input flag1 flag2 flag3 flag4 flag5;
datalines;
0 1 0 1 0
1 1 1 1 1
1 1 1 1 0
0 0 0 0 0
;

data want;
set old;
array t(*) flag:;
if whichn(0,of t(*)) eq 0;
run;

Re: where statement: multi variables have the same value

sasecn — Tue, 23 Jan 2018 01:30:13 GMT

Thanks, the code also works well. Just wondering can it be used in proc sql?

Re: where statement: multi variables have the same value

Reeza — Tue, 23 Jan 2018 02:14:05 GMT

Variable lists and/or arrays can't be used in PROC SQL but WHICHN can be used.

If the variable list is relatively short (30 is fine) then you can also create a macro variable with the list of variables from the SASHELP VCOLUMN table and use that in your WHERE clause instead.

You'll have to adjust the first portion of the query to match your variable list notation.

proc sql noprint;

select name into :var_list separated by ', '
from sashelp.vcolumn
where libname='WORK' and %upcase(memname)='MYDATA' and name like 'MYVAR%';
quit;

proc sql;
create table want as
select *
from WORK.MYDATA
where whichn(0, &var_list) = 0;
quit;

Re: where statement: multi variables have the same value

sasecn — Tue, 23 Jan 2018 16:14:11 GMT

Thank you! That's good to know.