Calling macro inside PROC SQL in SAS

Question

I need to call a macro code from proc sql in SAS like below.

PROC SQL;
 CONNECT TO DB2 (SSID = &DGSYS);  
  CREATE TABLE <DS_NAME> AS  
   SELECT * FROM CONNECTION TO DB2  
    (  
         SELECT * FROM <TAB_NAME> WHERE...       
    );

    %MACRO_CODE(....) --> am calling a macro block of code here

 DISCONNECT FROM DB2;  
QUIT;

But this is throwing up an error. Could this not be done?

When I used a macro variable in my query it is allowing me but not a call made to macro code.

The macro would create another macro variable which will be an IN CLAUSE holding values from the previously created table/dataset. And it would be subsequently used in the another CREATE TABLE... in side the same PROC SQL.

What is your macro doing? This isn't technically wrong, but the answer depends on your macro. What is the error? Is the macro intended to be SQL code? — Joe, Jul 11 '13 at 13:29
@Joe has it right - macros in SAS, as said [elsewhere](http://stackoverflow.com/a/10249455/496803) "_depend on text replacement rather than evaluation of expressions like any proper programming language_". So unless you are dynamically generating code that is valid inside the `proc sql` statement, the macro will likely fail. — thelatemail, Jul 12 '13 at 00:49
First off, what is the error. "Throwing up an error" is not helpful - *which* error? Second, we'd need more than what you initially posted in terms of description of the macro. What's going on inside it? Is it a %let statement, or a select into, or something else? — Joe, Jul 12 '13 at 13:53
"Macro creates another macro variable" Is there maybe a variable scope problem? Are they %globals ? — Neil Neyman, Jul 31 '13 at 15:15

score 2 · Answer 1 · edited Mar 17 '20 at 19:33

If I understand correctly, it sounds like a macro isn't necessary for what you're doing. If you're creating a macro variable containing a list of values from a dataset, you can just use the INTO: clause.

ds_name : Table with numeric and alphanumeric variables.

    proc sql noprint;

        /* If VAR_NAME is numeric, this works */
        select distinct var_name
        into: listvals
        separated by " "
        from ds_name;

        /* If VAR_NAME is character, do this instead */
        select distinct cats("'",varname,"'")
        into: listvals
        separated by " "
        from ds_name;

        create table new_ds as
        select *
        from another_ds
        where var_name in (&listvals);

   quit;

I hope that helps.

The macro is called outside ProcSQL, it can not be for a macro variable need. — D. O., Mar 13 '20 at 19:57

Calling macro inside PROC SQL in SAS

1 Answers1