topic Re: Create multiple conditions in a where statement in a dynamic way in SAS Programming

Create multiple conditions in a where statement in a dynamic way

gabras — Mon, 27 Aug 2018 10:36:10 GMT

Hello,

I need to create multiple conditions in a where statement in a dynamic way.
I'll explain.
Let's say we have:

data want;
set have;
where first = "& vala" and second = "& valb" and third = "& valc";
run;

Where & val # are values taken from a table with three columns (first, second, third) and a row.

How can I do if I want to add one or more lines to the table and make the code in the following way:

data want;
set have;
where (first = "& vala1" and second = "& valb1" and third = "& valc1"
OR first = "& vala2" and second = "& valb2" and third = "& valc2"
OR first = "& vala #" and second = "& valb #" and third = "& valc #")
run;

Thanks for the help.

One more question: is there a SAS application which permits the user to modify a table based on some prompt you designed? Such as add rows, upodate a column or something like this?

Re: Create multiple conditions in a where statement in a dynamic way

Astounding — Mon, 27 Aug 2018 10:43:25 GMT

Are you allowed to sort your data sets? If so, this becomes a simple match merge and doesn't need any macro language:

proc sort data=have;

by first second third;

run;

proc sort data=valid_values;

by first second third;

run;

data want;

merge have (in=in1) valid_values (in=in2);

by first second third;

if in1 and in2;

run;

Re: Create multiple conditions in a where statement in a dynamic way

gabras — Mon, 27 Aug 2018 12:59:22 GMT

Thank you @Astounding

you are right.

But the problem is not so simple.

I migth have the case in which i have also the fourth column that i not always need to use in the where condition, such as:

where first = &valuea1 and second =valueb1 and third=valuec1

OR first = &valuea2 and second =&valueb2 and third=&valuec2 and fourth= &valued2;

Re: Create multiple conditions in a where statement in a dynamic way

Astounding — Mon, 27 Aug 2018 14:22:05 GMT

This is nearly as simple. Presumably you can tell whether you need three variables or four. If you can't tell, that's another story and takes a little more work.

%let varlist=first second third fourth;

proc sort data=have;

by &varlist;

run;

proc sort data=valid_values;

by &varlist;

run;

data want;

merge have (in=in1) valid_values (in=in2 keep=&varlist);

by &varlist;

if in1 and in2;

run;

Re: Create multiple conditions in a where statement in a dynamic way

gabras — Tue, 28 Aug 2018 08:52:54 GMT

Hi @Astounding

let's say that what you suggested is correct: i have to think if it fits for my case.

Is thera a SAS application that permits a user to modify the table valid_values by adding, updating or deleting rows?

Thank you

Re: Create multiple conditions in a where statement in a dynamic way

Astounding — Tue, 28 Aug 2018 11:38:47 GMT

Of course.

Adding only: PROC APPEND

Deleting / modifying / adding: a DATA step or PROC SQL

Re: Create multiple conditions in a where statement in a dynamic way

gabras — Tue, 28 Aug 2018 12:18:38 GMT

I was looking for the SAS AppDev Studio which you can use to develop web application that could permit the end-user to modify table.

Thanks