where condition by using the variable input

[dht115]

Hello, 

I do need to use where/if condition by using the data from one of the variable. 

For example: 

data have;

input v1 v2 v3;

datalines;

1 2 3

4 5 6

7 8 9

;

run;

data want; 

set have;

*value for v1 (1,4,7) should automatically populated to avoid human errors;

where v1 in (1,4, 7);

run 

[Reeza]

It's not clear what your question is, can you please expand this with more detail.

[dht115]

I need to use where/if conditional statement with "in" option which allow me to put more than one values for the condition. 

I do not want to do manual input for my variable. I need some macros or other solution which automatically takes all values from variable V1 and create a dataset based on that condition. 

In my example, V1 has value of 1,4,7. Is there a way for us to refer these value with some kind of conditional statement?

If there is some other ways to approach this issue, please share. 

[Reeza]

In your example you're referring to the same data set, is that the real case? Because that would never be realistic.
Assuming you're working with multiple data sets, SQL is actually the easiest to code/understand.

proc sql;
create table want as
select * from
table1 as t1
where t1.ID in (select t2.ID from table2 as t2);
quit;

---

@dht115 wrote:

I need to use where/if conditional statement with "in" option which allow me to put more than one values for the condition. 

I do not want to do manual input for my variable. I need some macros or other solution which automatically takes all values from variable V1 and create a dataset based on that condition. 

In my example, V1 has value of 1,4,7. Is there a way for us to refer these value with some kind of conditional statement?

If there is some other ways to approach this issue, please share. 

---

[ballardw]

---

@dht115 wrote:

I need to use where/if conditional statement with "in" option which allow me to put more than one values for the condition. 

I do not want to do manual input for my variable. I need some macros or other solution which automatically takes all values from variable V1 and create a dataset based on that condition. 

In my example, V1 has value of 1,4,7. Is there a way for us to refer these value with some kind of conditional statement?

If there is some other ways to approach this issue, please share. 

---

First you do not want something that "automatically takes all values from variable V1" if you are worried about incorrect values entered somewhere. If they are incorrect any process like that would include the incorrect values.

Somewhere along the line you have to specify what are valid values.

One way to filter data is perhaps a series of custom formats for different variables such as this:

data example;
      input v1 v2 v3;
datalines;
1 2 3
4 5 6
7 8 9
6 8 9
;
run;

proc format;
value v1valid
1,4,7 = 'Valid'
other = 'Invalid'
;

data want; 
  set have;
  where put(v1,v1valid.)='Valid';
run;

If you have different formats for different variables then you would be changing the variable name and format but the rest of the code wouldn't change. But I am not sure this saves much coding unless your real variables have lots of values. I do have code that checks a site id variable for a couple hundred locations because the data sources will slip in new sites without telling me and I need to do some stuff based on the site. So I get warnings and can ask the source about those needed bits of information such as "where is it?".

Another approach is to do something when the data is actually read into a SAS data set that warns you about invalid data and correct it for "later". A custom informat can do that.

proc format;
invalue v1read
1,4,7=_same_
other=_error_
;
run;

data example;
      input v1 :v1read.;
datalines;
1
4
7
6
;
run;

for example reads v1 and when a not-valid value is encountered there is a warning in the log and the 6 is set to missing.

IF you have a data set with the variable name and valid values we can use that to make either of the "Valid" type format or an informat. But anything automatically using data with potential errors requires the computer to read your mind as to which value might be "valid" or not. Which currently is not going to happen.

You might provide a more detailed use case as the one you show didn't filter out anything or show what would happen "conditionally" when the values weren't in the specified list.

[Kurt_Bremser]

This does not make sense.

By automatically populating the IN list with all values present, you will not filter out anything, so the WHERE condition becomes useless..