BookmarkSubscribeRSS Feed
yus03590
Calcite | Level 5

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?

8 REPLIES 8
Reeza
Super User

 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
Calcite | Level 5

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
PROC Star

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
PROC Star

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
Obsidian | Level 7

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

Reeza
Super User

@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
Lapis Lazuli | Level 10

 

  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
Calcite | Level 5

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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 8 replies
  • 1117 views
  • 2 likes
  • 5 in conversation