BookmarkSubscribeRSS Feed
Mgarret
Obsidian | Level 7

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;

2 REPLIES 2
Reeza
Super User

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.

Mgarret
Obsidian | Level 7

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;

sas-innovate-2024.png

Today is the last day to save with the early bird rate! Register today for just $695 - $100 off the standard rate.

 

Plus, pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 2 replies
  • 944 views
  • 3 likes
  • 2 in conversation