Change sheet names in SAS ODS dynamically

Question

Is there a way to dynamically change the sheet names in SAS ODS using macros? Inside a macro, I have the following code:

    %DO N = 1 %TO &SQLOBS;

    ODS TAGSETS.ExcelXP 
        OPTIONS(SHEET_NAME = &&TABLEVAR&N
                EMBEDDED_TITLES = 'NO'
                EMBEDDED_FOOTNOTES = 'NO'
                PRINT_HEADER = 'CURRENT &&TABLEVAR&N AS OF &D';

    PROC PRINT 
        DATA = WORK.&&TABLEVAR&N
        NOOBS;

    RUN; 

    %END;

Which basically loops through an array of table names and for each table outputs a separate Excel Sheet. And by calling a macro variable &TABLEVAR, it is supposed to dynamically change the sheet name for each table. However, I get the following error:

ERROR 22-322: Expecting a quoted string.

So SAS does resolve the macro variable, I can see the table names are right in the log, but the problem is they are not a quoted string. So I tried it like this with quotes around it:

OPTIONS(SHEET_NAME = '&&TABLEVAR&N'

But then the macro variable is not being resolved by SAS and all the sheets are named &&TABLEVAR&1, 2, etc.

Is there a way around this? (And each sheet is a separate table with a different name so I can't use a BY group.) What good macros are if they can't interact with ODS?

puk789 puk789 · Accepted Answer · 2016-12-07T09:18:07

I just found out placing %sysfunc and quote in front of &&TABLEVAR&N does the trick.

So the final code looks like this:

 %DO N = 1 %TO &SQLOBS;

ODS TAGSETS.ExcelXP 
    OPTIONS(SHEET_NAME = %sysfunc(quote(&&TABLEVAR&N))
            EMBEDDED_TITLES = 'NO'
            EMBEDDED_FOOTNOTES = 'NO'
            PRINT_HEADER = 'CURRENT &&TABLEVAR&N AS OF &D';

PROC PRINT 
    DATA = WORK.&&TABLEVAR&N
    NOOBS;

RUN; 

%END;

Change sheet names in SAS ODS dynamically

2 Answers