DATA Step, Macro, Functions and more

where statement: multi variables have the same value

Accepted Solution Solved
Reply
Contributor
Posts: 27
Accepted Solution

where statement: multi variables have the same value

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!


Accepted Solutions
Solution
‎01-22-2018 08:30 PM
PROC Star
Posts: 1,357

Re: where statement: multi variables have the same value

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;

View solution in original post


All Replies
Super User
Posts: 22,874

Re: where statement: multi variables have the same value

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!




Contributor
Posts: 27

Re: where statement: multi variables have the same value

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.
Solution
‎01-22-2018 08:30 PM
PROC Star
Posts: 1,357

Re: where statement: multi variables have the same value

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;
Contributor
Posts: 27

Re: where statement: multi variables have the same value

Posted in reply to novinosrin

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

Super User
Posts: 22,874

Re: where statement: multi variables have the same value

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;
Contributor
Posts: 27

Re: where statement: multi variables have the same value

Thank you! That's good to know.

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 6 replies
  • 179 views
  • 0 likes
  • 3 in conversation