DATA Step, Macro, Functions and more

Problem with PROC SQL Macro If

Reply
Occasional Contributor
Posts: 15

Problem with PROC SQL Macro If

[ Edited ]

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?

Super User
Posts: 24,028

Re: Problem with PROC SQL Macro If

 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
Occasional Contributor
Posts: 15

Re: Problem with PROC SQL Macro If

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

Super User
Posts: 6,939

Re: Problem with PROC SQL Macro If

[ Edited ]

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.

Super User
Posts: 6,939

Re: Problem with PROC SQL Macro If

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.

Occasional Contributor
Posts: 16

Re: Problem with PROC SQL Macro If

[ Edited ]

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

Super User
Posts: 24,028

Re: Problem with PROC SQL Macro If

Posted in reply to prashantgupta

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.

Frequent Contributor
Posts: 112

Re: Problem with PROC SQL Macro If

 

  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;
Occasional Contributor
Posts: 15

Re: Problem with PROC SQL Macro If

Posted in reply to Satish_Parida

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

Ask a Question
Discussion stats
  • 8 replies
  • 133 views
  • 2 likes
  • 5 in conversation