Sas use macro variable in proc sql where clause

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.

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

create table &job as

select * from test.test_table

where RULE_NAME = '&job'

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

8 REPLIES 8 Super User Re: PROC SQL macro variable in where statement Posted 10-11-2017 01:39 AM (7864 views) | In reply to SASAna

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

Quartz | Level 8 Re: PROC SQL macro variable in where statement Posted 10-11-2017 05:46 AM (7815 views) | In reply to Kurt_Bremser

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 Re: PROC SQL macro variable in where statement Posted 10-11-2017 05:55 AM (7813 views) | In reply to SASAna

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

Fluorite | Level 6 Re: PROC SQL macro variable in where statement Posted 04-30-2019 03:11 PM (6689 views) | In reply to Kurt_Bremser

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 sql; create table AB as select developer_id from Table1 where where numeric_column_name=&id; quit;
Diamond | Level 26 Re: PROC SQL macro variable in where statement Posted 04-30-2019 03:32 PM (6682 views) | In reply to dwights

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.