CODEWITHSUNDEEP
sas
athresh
athresh

Reputation: 553

Calling macro inside PROC SQL in SAS

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.

Upvotes: 0

Views: 3265

Answers (1)

Alex A.
Alex A.

Reputation: 5586

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.

Upvotes: 2

Related Questions