PROC SQL macro variable in where statement

[SASAna]

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;

[Kurt_Bremser]

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

[SASAna]

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

[Kurt_Bremser]

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

[dwights]

Hey Kurt,

What should I do if 'job' is a numeric macro variable. The following code doesn't work (note that idList is a numeric macro list) -

%let id= %scan(&idList,&i);



proc sql;

create table AB as

select developer_id from Table1 where 

where numeric_column_name=&id;



quit;

Thanks!

[PaigeMiller]

Macro variables have values that are always considered text. There is no distinction between numeric and character as there is for data step variables. Even if your macro variable contains the value 2019, this is considered text.

Macro variables just perform text substitution, wherever you see &id, the value of the macro variable is substituted, and SAS code is generated, and this SAS code must be valid working SAS code that executes without errors.

If the code doesn't work, it is because the text substitution did not produce valid SAS code. If you are getting an error about different data types (are you getting such a message? you didn't say what the error message was — it would really help if we saw the entire LOG, and not just the error message) then you probably need to to put "&id" in double-quotes because the data set variable numeric_column_name is probably character.

[andreas_lds]

When testing/debugging macros always turn on options mprint, mlogic and symbolgen.

[Astounding]

You have an extra "where" in your code.

[PaigeMiller]

---

@Astounding wrote:
You have an extra "where" in your code.

---

Seems like @Astounding has pinpointed the problem (there may be others).

Advice to @dwights: this difficulty can be prevented when you write macros by first writing code WITHOUT MACROS and WITHOUT MACRO VARIABLES for at least one iteration of your code and getting this code to work properly. This is highly recommended and prevents a lot of problems, and would have identified this double WHERE command problem immediately.