IF/THEN in PROC SQL statement of SAS

Having problems with an IF/THEN statement in a PROC SQL; statement of SAS.  What am I missing or doing wrong?  Not even getting any errors...just not doing what it should:

proc sql;
      create table NEW_TBL as
            select distinct
                  FIELD1, FIELD2, FIELD3, FIELD4, FIELD5,  
                  %IF &lob. = 'monkey' %THEN
                                                FIELD6, FIELD7
      from OLD_TBL;
Lee R Liddick JrReporting AnalystAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

nemws1Database AdministratorCommented:

You are using macro %If's - so I assume you are running this in a Macro.

The best way to check what the output of a Macro is - is to use:


Open in new window

Then set the macro variable to 'monkey', and see what pops out in the log
Lee R Liddick JrReporting AnalystAuthor Commented:
This is in a macro...not sure where to put this mprint.  I'm tried a few things and nothing really is showing in the log.
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

Lee R Liddick JrReporting AnalystAuthor Commented:
The case statement doesn't work in this.  I've tried a couple times a couple different ways...including the link you provided.
Aloysius LowCommented:
does changing  %IF &lob. = 'monkey' %THEN
to  %IF "&lob" = "monkey" %THEN help?

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Lee R Liddick JrReporting AnalystAuthor Commented:
YES...that did it.  Double quotes...really?  Thank you!
Aloysius LowCommented:
actually, because the value passed in was just monkey, thus to do a string comparison, you need to add quotation marks... however, when doing macros, single quotation marks results in the variable being un-parsed i.e. treated as literal value ('&lob' really means &lob instead of monkey), so you'll need to use double quotes to enclose both the variable ("&lob") and the string value ("monkey") :)

your original code would have worked if you had passed in 'monkey' instead of monkey into the macro
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.