Multiple workbooks with ExcelXP tagset

Reply
New Contributor
Posts: 2

Multiple workbooks with ExcelXP tagset

Hello,

I've been trying to generate multiple workbooks (not worksheets), using

a macro call with ods tagsets.ExcelXP. I've included the following relevant

snippet of code.

%macro run_it;

    %let i = 1;

    %do %while(&i<=&max);

    ods tagsets.ExcelXP

        file="book&i..xml"

        style=styles.statistical

        options (default_column_width="10,10,10,10,10"

                width_fudge = "1.0"

                sheet_interval = "Proc"

                sheet_label = " ");

    ods tagsets.ExcelXP options(sheet_name="My_title");

    PROC report data=input_data

        (where = (id_var = &&other_var&i));

        column some

                vars;

        define some;

        define vars;

    run; quit;

    ods tagsets.ExcelXP close;

    %let i = %eval(&i + 1);

    %end;

%mend run_it;

%run_it;

The unwanted behavior is that the sheet name in each individual workbook

book&i is the title, "My_title &i". I want the identical sheet name for

every workbook produced, without the iteration number appended. I expected

the procedure counter to reinitialize after closing and reopening ods

tagsets.ExcelXP with each iteration through the macro loop.

Any help would be appreciated.

Thank you,

Bart

Frequent Contributor
Posts: 95

Re: Multiple workbooks with ExcelXP tagset

Do you have the log of your SAS session.

I tried your code with sashelp.class data set and changed variable names.

Two workbooks created with sheet_names 'My_title'

Zafer

%macro run_it;

    %let i = 1;

    %do %while(&i<=2);

    ods tagsets.ExcelXP

        file="d:\temp\book&i..xml"

        style=styles.statistical

        options (default_column_width="10,10,10,10,10"

                width_fudge = "1.0"

                sheet_interval = "Proc"

                sheet_label = " ");

    ods tagsets.ExcelXP options(sheet_name="My_title");

    PROC report data=sashelp.class;

        column sex

                age;

        define sex/group;

        define vars/sum analysis;

    run; quit;

    ods tagsets.ExcelXP close;

    %let i = %eval(&i + 1);

    %end;

%mend run_it;

%run_it;

New Contributor
Posts: 2

Re: Multiple workbooks with ExcelXP tagset

Hi Alpay,

After banging my head against the wall multiple times, I realized that the reason the number wasn't changing was because in the original version of the code, I had:

ods tagsets.ExcelP close;

instead of

ods tagsets.ExcelXP close;

No ERROR, WARNING, or any message clueing me in on this to speak of in the log (I cannot post my log for security reasons).

Thank you for your help, Bart

Ask a Question
Discussion stats
  • 2 replies
  • 361 views
  • 0 likes
  • 2 in conversation