DATA Step, Macro, Functions and more

PROC SQL macro variable in where statement

Reply
Frequent Contributor
Posts: 95

PROC SQL macro variable in where statement

Hi SAS users,

 

I needed some help in creating the below table.Currently it is generating 0 records. It is not able to filter the job in the SQL statement in rule_name variable & branch variable in platform.

 

%let job = abc;      

 

%let branch=testing_;

 

%put &job;

%put &branch;

 

libname test oracle user="XXX" password= "XXX" path='XXX' schema=test;

 

proc sql ;

create table &job as

select * from test.test_table

where RULE_NAME = '&job'

and Platform= substr('&branch',1,4) 

;

quit;

 

Super User
Posts: 7,868

Re: PROC SQL macro variable in where statement

Use double quotes when you want your macro variables to resolve in string literals. Macro triggers are not recognized when enclosed in single quotes.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Frequent Contributor
Posts: 95

Re: PROC SQL macro variable in where statement

Posted in reply to KurtBremser
Thanks Kurt. With the double quotes the macro variable is resolving but it is not able to filter and create the table. Is there anything that i am missing
Super User
Posts: 7,868

Re: PROC SQL macro variable in where statement

Inspect your data. It may be necessary to use normalizing functions like upcase() or trim(), left(), strip().

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Super Contributor
Posts: 345

Re: PROC SQL macro variable in where statement

When testing/debugging macros always turn on options mprint, mlogic and symbolgen.
Ask a Question
Discussion stats
  • 4 replies
  • 101 views
  • 0 likes
  • 3 in conversation