BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
sasecn
Quartz | Level 8

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!

1 ACCEPTED SOLUTION

Accepted Solutions
novinosrin
Tourmaline | Level 20
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

6 REPLIES 6
Reeza
Super User

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!




sasecn
Quartz | Level 8
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.
novinosrin
Tourmaline | Level 20
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;
sasecn
Quartz | Level 8

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

Reeza
Super User

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;
sasecn
Quartz | Level 8

Thank you! That's good to know.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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