Problem with PROC SQL Macro If

[yus03590]

Hi!  I'm trying to build where proc sql where clause conditionally using a macro if/then and a macro variable (TYPE).  However, I can't seem to get it to evaluate right.  Here's the where clause:

where

%IF &TYPE = Y %THEN %DO;
REPORT_VW.TYPE_GROUP_CD = 'Y' AND
%END;
%IF &TYPE = S %THEN %DO;
REPORT_VW.TYPE_GROUP_CD = 'S' AND
%END;

quit;

What's wrong here?

[Reeza]

 However, I can't seem to get it to evaluate right.

What does that mean? How are you setting the macro variable? What isn't working correctly, are you getting an error or incorrectly classified data? If incorrectly classified, does it appear random or is there some pattern?

Any reason to not just do:

REPORT_VW.TYPE_GROUP_CD = "&TYPE" AND

[yus03590]

So I'm trying to execute certain filters conditionally depending on the value of the macro TYPE variable, the value of which is set by a sas job web interface and is either Y, S or A.  The filtering conditions within the macro if are not evaluating even when TYPE = Y or S.  My final where clause outside the if is the only one being executed.

If I understand correctly from all the comments, the correct format is:

MACRO_NAME(TYPE)

proc sql;

...

where

%if &TYPE = Y %then %do;

person.age_group = Y AND

%end;

%if &TYPE = S %then %do;

person.age_group = S AND

%end;

final sql condition;

quit;

%END

**call the macro

MACRO_NAME(TYPE);

[Astounding]

In this more complete version of the program, you need quotes in the SQL code, but not in the macro comparisons:

proc sql;

...

where

%if &TYPE = Y %then %do;

person.age_group = 'Y' AND

%end;

%if &TYPE = S %then %do;

person.age_group = 'S' AND

%end;

final sql condition;

quit;

Also, it looks like that final %END was intended to be:

%MEND;

Still, it would be a good idea to apply the earlier suggestion, as long as &TYPE will always be Y or S:

proc sql;

...

where

person.age_group = "&TYPE" AND

final sql condition;

quit;

Be sure to use double quotes (not single quotes) .... necessary to allow &TYPE to resolve into its value.

[Astounding]

There's nothing wrong with the code you have shown.  But there are lots of possibilities that extend beyond what you have shown.  For example:

Macro %IF/%THEN statements can only appear inside a macro definition.

The code you have shown doesn't finish the WHERE clause.

You'll need to test the code making sure the MPRINT option is on, and show the log.

[prashantgupta]

I think in %if &TYPE = Y and S should be in quote like 'Y' and 'S' .

[Reeza]

---

@prashantgupta wrote:

I think in %if &TYPE = Y and S should be in quote like 'Y' and 'S' .

---

Not in macro comparisons. Literals don't have quotes in functions either.

[Satish_Parida]

1. You do not have a closing semicolon after end of SQL statement
2. You have a open and statement condition in the query.
3. Please follow the answer provided by @Reeza, It is the most effective way of doing it.
4. Please check the macro variable &TYPE, most of the time they are wrongly entered with quotation attached ('Y' insted of Y) that fails both condition in your macro if condition.

proc sql;
...
where REPORT_VW.TYPE_GROUP_CD = "&TYPE";
quit;

[yus03590]

If I understand correctly, the correct format is:

MACRO_NAME(TYPE);

proc sql;

...

where

%if &TYPE = Y %then %do;

person.age_group = Y AND

%end;

%if &TYPE = S %then %do;

person.age_group = S AND

%end;

final sql condition;

quit;

%END

**call the macro

MACRO_NAME(TYPE);