topic Re: Problem with PROC SQL Macro If in SAS Programming

Problem with PROC SQL Macro If

yus03590 — Mon, 29 Jan 2018 22:40:01 GMT

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?

Re: Problem with PROC SQL Macro If

Reeza — Mon, 29 Jan 2018 22:42:56 GMT

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

Re: Problem with PROC SQL Macro If

Astounding — Mon, 29 Jan 2018 22:43:42 GMT

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.

Re: Problem with PROC SQL Macro If

prashantgupta — Tue, 30 Jan 2018 08:12:29 GMT

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

Re: Problem with PROC SQL Macro If

Satish_Parida — Tue, 30 Jan 2018 08:35:25 GMT

You do not have a closing semicolon after end of SQL statement
You have a open and statement condition in the query.
Please follow the answer provided by @Reeza, It is the most effective way of doing it.
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;

Re: Problem with PROC SQL Macro If

Reeza — Tue, 30 Jan 2018 15:48:38 GMT

@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.

Re: Problem with PROC SQL Macro If

yus03590 — Tue, 30 Jan 2018 21:03:02 GMT

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);

Re: Problem with PROC SQL Macro If

yus03590 — Tue, 30 Jan 2018 21:04:57 GMT

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);

Re: Problem with PROC SQL Macro If

Astounding — Tue, 30 Jan 2018 21:56:31 GMT

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.