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
@Joe has it right - macros in SAS, as said elsewhere "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
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

Alex A. Alex A. · Accepted Answer · 2013-11-18T20:50:40

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.

Calling macro inside PROC SQL in SAS

1 Answers