The SAS Output Delivery System and reporting techniques

tagsets.excelxp multisheet workbook: Macro to Produce Multiple Sheets

Reply
Frequent Contributor
Posts: 142

tagsets.excelxp multisheet workbook: Macro to Produce Multiple Sheets

Hi all,

I’m using ods tagsets.excelxp to create a formatted excel report. Right now the number of times I have to run the report is based off of a variable called Test_Number (numeric values 1, 2, 3, 4….).  I have a macro variable Test which I have to hard code for each pass.  What I would like to have is a macro that runs through all the possible values in test and outputs to a multisheet workbook;  So, each sheet is a test in the workbook . I have used ods tagsets.excelxp to produce multisheet workbooks before but I can’t figure out how to write a macro which produces the number of sheets based on the values  in a variable. Below is my program.


Any assistance will be greatly appreciated. Thanks!

/***Choose Test Number*****/

%let Test= 1 ;run;


ods listing close;

options

rightmargin=.1in

leftmargin=.1in

Bottommargin=.1in

topmargin=.1in;

ods tagsets.excelxp file="C:\Users\Desktop\Test Table.xml"

style=minimal

options(sheet_interval='none'  ORIENTATION='portrait' AUTOFIT_HEIGHT='yes' PAGE_ORDER_ACROSS='yes');

proc report data= title nowd ;

column Test_Title;

define Test_Title / display

style(column)={ just=right  font_size=10pt  cellwidth=1.7in}

  style(header)={ just=center foreground=white background=white};

where Test_Number=&Test;

run;

proc report data=Label nowd

style(column)={just=center font_size=10pt font_face='Arial Narrow'cellwidth=2.7in}

Style(header)={just=center font_size=10 pt font_face= 'Arial Narrow'  FONT_WEIGHT =BOLD background=cxcccccc};

  column  dum Control Test;

  define dum / display

  style(column)={ just=right  font_size=10pt  cellwidth=1.4in}

  style(header)={ just=center foreground=white background=white};

  where  Test_Number=&Test;

  run;

proc report data=Final nowd

style(column)={just=center font_size=10pt font_face='Arial Narrow'cellwidth=2.7in}

Style(header)={just=center font_size=10 pt font_face= 'Arial Narrow'  FONT_WEIGHT =BOLD background=cxcccccc};

  column

KPI Control Test lift prob Statistically_Significant_95 percent_Significant;

  define KPI / display

  style(column)={ just=right  font_size=10pt font_face= 'Arial Narrow' FONT_WEIGHT =BOLD  cellwidth=1.6in}

  style(header)={ just=center font_size=10 pt font_face= 'Arial Narrow'  FONT_WEIGHT =BOLD background=cxcccccc};

  define Control / display

  style(column)={just=center  font_size=10pt font_face= 'Arial Narrow'cellwidth=.7in background=#FFCC99}

  style(header)={just=center font_size=10 pt font_face= 'Arial Narrow'  FONT_WEIGHT =BOLD background=cxcccccc};

  define Test  / display

  style(column)={just=center  font_size=10pt font_face= 'Arial Narrow'cellwidth=.7in }

  style(header)={just=center font_size=10 pt font_face= 'Arial Narrow'  FONT_WEIGHT =BOLD background=cxcccccc};

  define lift  / display

  style(column)={just=center font_size=10pt font_face= 'Arial Narrow'cellwidth=.7in}

  style(header)={just=center font_size=10 pt font_face= 'Arial Narrow'  FONT_WEIGHT =BOLD background=cxcccccc};

  define prob  / display

  style(column)={just=center font_size=10pt font_face= 'Arial Narrow'cellwidth=.7in}

  style(header)={just=center font_size=10 pt font_face= 'Arial Narrow'  FONT_WEIGHT =BOLD background=cxcccccc};

  define Statistically_Significant_95 / display

  style(column)={just=center font_size=10pt font_face= 'Arial Narrow' cellwidth=1.in  FONT_WEIGHT =BOLD}

  style(header)={just=center font_size=10 pt font_face= 'Arial Narrow'  FONT_WEIGHT =BOLD background=cxcccccc};

  define Percent_Significant/ display

  style(header)= {just=center  font_size=10pt FONT_WEIGHT=BOLD font_face= 'Arial Narrow' background=cxcccccc}

  style(column)= {just=center font_size=10pt FONT_WEIGHT=BOLD font_face= 'Arial Narrow'  cellwidth=.9in};

where Test_Number=&Test;

run;

title;

ods _all_ close;

Grand Advisor
Posts: 17,313

Re: tagsets.excelxp multisheet workbook: Macro to Produce Multiple Sheets

See the second example in the Call Execute documentation:

SAS(R) 9.2 Macro Language: Reference

Make sure to include your ODS Open/Close before/after your call execute data step.

Frequent Contributor
Posts: 142

Re: tagsets.excelxp multisheet workbook: Macro to Produce Multiple Sheets

Thanks Rezza! I checked your example and I'm still confused with the syntax.  I'm getting this error

SYMBOLGEN:  Macro variable TEST resolves to

MPRINT(TESTOUT):   where Test_Number=;

ERROR: Syntax error while parsing WHERE clause.

MPRINT(TESTOUT):   run;

ERROR 22-322: Syntax error, expecting one of the following: a name, a quoted string,

              a numeric constant, a datetime constant, a missing value, INPUT, PUT.

Also, I use multiple  datasets in this program - title , Label and Final. I'm not sure how to incoperate all 3 into the macro.


Ant additional feedback will be greatly appreciated. Thank you!!!

This is what I tried:

%macro testout(Test);


options

rightmargin=.1in

leftmargin=.1in

Bottommargin=.1in

topmargin=.1in;

ods tagsets.excelxp file="C:\Users\Desktop\Test Table.xml"

style=minimal

options(sheet_interval='none'  ORIENTATION='portrait' AUTOFIT_HEIGHT='yes' PAGE_ORDER_ACROSS='yes');

proc report data= title nowd ;

column Test_Title;

define Test_Title / display

style(column)={ just=right  font_size=10pt  cellwidth=1.7in}

  style(header)={ just=center foreground=white background=white};

where Test_Number=&Test;

run;

proc report data=Label nowd

style(column)={just=center font_size=10pt font_face='Arial Narrow'cellwidth=2.7in}

Style(header)={just=center font_size=10 pt font_face= 'Arial Narrow'  FONT_WEIGHT =BOLD background=cxcccccc};

  column  dum Control Test;

  define dum / display

  style(column)={ just=right  font_size=10pt  cellwidth=1.4in}

  style(header)={ just=center foreground=white background=white};

  where  Test_Number=&Test;

  run;

proc report data=Final nowd

style(column)={just=center font_size=10pt font_face='Arial Narrow'cellwidth=2.7in}

Style(header)={just=center font_size=10 pt font_face= 'Arial Narrow'  FONT_WEIGHT =BOLD background=cxcccccc};

  column

KPI Control Test lift prob Statistically_Significant_95 percent_Significant;

  define KPI / display

  style(column)={ just=right  font_size=10pt font_face= 'Arial Narrow' FONT_WEIGHT =BOLD  cellwidth=1.6in}

  style(header)={ just=center font_size=10 pt font_face= 'Arial Narrow'  FONT_WEIGHT =BOLD background=cxcccccc};

  define Control / display

  style(column)={just=center  font_size=10pt font_face= 'Arial Narrow'cellwidth=.7in background=#FFCC99}

  style(header)={just=center font_size=10 pt font_face= 'Arial Narrow'  FONT_WEIGHT =BOLD background=cxcccccc};

  define Test  / display

  style(column)={just=center  font_size=10pt font_face= 'Arial Narrow'cellwidth=.7in }

  style(header)={just=center font_size=10 pt font_face= 'Arial Narrow'  FONT_WEIGHT =BOLD background=cxcccccc};

  define lift  / display

  style(column)={just=center font_size=10pt font_face= 'Arial Narrow'cellwidth=.7in}

  style(header)={just=center font_size=10 pt font_face= 'Arial Narrow'  FONT_WEIGHT =BOLD background=cxcccccc};

  define prob  / display

  style(column)={just=center font_size=10pt font_face= 'Arial Narrow'cellwidth=.7in}

  style(header)={just=center font_size=10 pt font_face= 'Arial Narrow'  FONT_WEIGHT =BOLD background=cxcccccc};

  define Statistically_Significant_95 / display

  style(column)={just=center font_size=10pt font_face= 'Arial Narrow' cellwidth=1.in  FONT_WEIGHT =BOLD}

  style(header)={just=center font_size=10 pt font_face= 'Arial Narrow'  FONT_WEIGHT =BOLD background=cxcccccc};

  define Percent_Significant/ display

  style(header)= {just=center  font_size=10pt FONT_WEIGHT=BOLD font_face= 'Arial Narrow' background=cxcccccc}

  style(column)= {just=center font_size=10pt FONT_WEIGHT=BOLD font_face= 'Arial Narrow'  cellwidth=.9in};

where Test_Number=&Test;

run;

title;

%mend testout;run;


ods listing close;


data _null_;

   set Final;

   call execute(%testout);

run;

ods _all_ close;

Ask a Question
Discussion stats
  • 2 replies
  • 462 views
  • 3 likes
  • 2 in conversation