Proc sql with condition

Accepted Solution Solved
Reply
Contributor
Posts: 58
Accepted Solution

Proc sql with condition

Hi,

I need to add a where condition in a proc sql depending by the value of a specific variable.

Here you are the code.

%let addwherecond = 'yes';

proc sql;

  update mydataset

    set field1 = 'blablabla'

      where fieldA = 'X'

         and fieldB = 'Y'

               if (addwherecond = 'yes' )

                        and fieldC = 'Z';

;

quit;

What is the right syntax?

Many thanks.


Accepted Solutions
Solution
‎12-19-2011 11:49 AM
New Contributor
Posts: 2

Proc sql with condition

Assuming you mean that you want to add an extra condition to the 'where' statement, based on the value of the macro variable addwherecond?

If so this might work:

%let addwherecond = 'yes';

proc sql;

update mydataset

set field1 = 'blablabla'

where fieldA = 'X'

  and fieldB = 'Y'

%macro condtest;

%if &addwherecond = 'yes' %then %do;

  and fieldC = 'Z'

%mend;

%condtest;

;

quit;

View solution in original post


All Replies
Solution
‎12-19-2011 11:49 AM
New Contributor
Posts: 2

Proc sql with condition

Assuming you mean that you want to add an extra condition to the 'where' statement, based on the value of the macro variable addwherecond?

If so this might work:

%let addwherecond = 'yes';

proc sql;

update mydataset

set field1 = 'blablabla'

where fieldA = 'X'

  and fieldB = 'Y'

%macro condtest;

%if &addwherecond = 'yes' %then %do;

  and fieldC = 'Z'

%mend;

%condtest;

;

quit;

PROC Star
Posts: 7,363

Proc sql with condition

Actually, with a slight modification, km's proposed solution will work:

data mydataset;

  length field1 $10;

  input (fieldA fieldB fieldC field1) ($);

  cards;

Y X Z .

X Y Z .

X Y . .

A B Z .

;

%let addwherecond = 'yes';

proc sql;

  update mydataset

    set field1 = 'blablabla'

      where fieldA = 'X'

       and fieldB = 'Y'

        %macro condtest;

          %if &addwherecond = 'yes' %then %do;

            and fieldC = 'Z'

          %end;

        %mend;

        %condtest

;

quit;

Super User
Super User
Posts: 6,500

Proc sql with condition

You could just test the value of the macro variable as part of another condition in your compound where clause.

Note: You do not normally use quotes around the values of macro variables.

Compare your results when the macro variable ADDWHERECOND is yes or something else.

%let addwherecond = yes;

proc sql;
  update mydataset
    set field1 = 'blablabla'
    where fieldA = 'X'
       and fieldB = 'Y'
       and ("&addwherecond" = 'yes' and fieldC = 'Z')
  ;

quit;

PROC Star
Posts: 7,363

Proc sql with condition

I agree with Tom's advice, but think that you have to add another condition.  I.e.:

data mydataset2;

  length field1 $10;

  input (fieldA fieldB fieldC field1) ($);

  cards;

Y X Z .

X Y Z .

X Y . .

A B Z .

;

%let addwherecond = no;

proc sql;

  update mydataset2

    set field1 = 'blablabla'

    where fieldA = 'X'

       and fieldB = 'Y'

       and (("&addwherecond"='yes' and fieldC = 'Z')

         or ("&addwherecond" ne 'yes'))

  ;

quit;

☑ This topic is SOLVED.

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

Discussion stats
  • 4 replies
  • 283 views
  • 3 likes
  • 4 in conversation