Referencing a macro variable created by a prompt SAS EG

Question

I created a prompt in SAS EG that takes a text input and creates the macro variable called 'variableName'.

I am trying to reference this macro variable like so:

proc sql;
create table MyTable as
   select * from Source_Table as a
   where a.field = &variableName ;

This gives me an error that says: "Syntax error, expecting one of the following: a name, a quoted string, a numeric constant, a datetime constant, a missing value, BTRIM, INPUT, PUT, SUBSTRING, USER."

I have also tried enclosing &variableName in single and double quotes but when I do that I just don't get any results.

I am able to reference the prompt when I use query builder and filter data based on the prompt, but I am trying to use the prompt's value in calculated expressions, etc. and in queries I write without query builder. How can i reference the variable I created in the prompt??

Edit: code with a value that the macro variable would have

proc sql;
create table MyTable as
    select * from Source_Table as a
    where a.field = 'NAME OF PERSON';

When I run that, I get the results I want.

Reeza Reeza · Accepted Answer · 2018-09-17T16:33:38

It needs to resolve to valid SAS code. Assuming &variableName is a string, then it would be something like:

proc sql;
create table MyTable as
   select * from Source_Table as a
   where a.field = "&variableName." ;

If this isn't working, please show a query that does work with the same value as the macro variable would have. And then we can suggest how to change your code.

Edit: based on your comment you do not have the prompt connected to your query. Right click the query and link the prompt to your query and it will run before the query to provide the value.

Referencing a macro variable created by a prompt SAS EG

1 Answers