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!
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;
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!
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;
Thanks, the code also works well. Just wondering can it be used in proc sql?
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;
Thank you! That's good to know.
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.