ODS EXCELXP output multiple tables per sheet across multiple sheets

CJ19 — Fri, 29 Jan 2016 17:42:21 GMT

Hello,

I've been stressing trying to find a resolution for my problem. I have a report that I need to export to excel. Each sheet contains three different SAS tables, with titles embedded, and I need to do this across 12 different sheets. So pretty much, each of the 12 excel sheets will consist of 3 unique SAS tables with embedded titles. I am able to produce the report of three tables on one sheet with titles; however, only the last sheet is remaining in the final excel output.

%MACRO SUMMARY(TESTNS, STUDY, LB, UB);

PROC MEANS NOPRINT N MEAN STDDEV SKEW KURT MAX P99 P95 P90 P75 P50 P25 P10 P5 P1 MIN VARDEF = N DATA = CLEAN_&STUDY._&assessment.;
VAR &varlist.;
CLASS MFC;
OUTPUT OUT = ALL_STATS_&STUDY. N= MEAN= STDDEV= SKEW= KURT= MAX= P99= P95= P90= P75= P50= P25= P10= P5= P1= MIN= /AUTONAME;
RUN;

DATA ALL_STATS_&STUDY. NOPRINT;
SET ALL_STATS_&STUDY.;
IF MASTER_FORM_CODE = '' THEN DELETE;
RUN;

/*SUMMARY JOIN*/
%let SCORELIST = &varlist.;
   %do j = 1 %to 12;
%let SCORE = %scan(&SCORELIST., &J);

ods tagsets.ExcelXP file='C:\Users\...\Desktop\Test Output2.xml' style=statistical
   options (/*sheet_interval = 'NONE'*/ sheet_name = "&score." /*sheet_label = " " */ embedded_titles='yes');

PROC TRANSPOSE DATA = ALL_STATS_&STUDY. OUT =TEST2   ;
ID MFC;
IDLABEL MFC;
RUN;
/*ods trace on;*/
DATA &SCORE. noprint;
SET TEST2;
_NAME_ = COMPRESS(TRANWRD(UPCASE(_NAME_), "&SCORE._",""));
RENAME _NAME_ = &score.;
LABEL _NAME_ = &score.;
WHERE UPCASE(_NAME_) CONTAINS "&SCORE.";
ods table =
RUN;
/*ods trace on;*/

       %DO I = 1 %TO &N_GRADES.;
PROC SQL noprint;
SELECT CGL INTO :CG
FROM GRADES
WHERE INDEX = &I.;
QUIT;

PROC MEANS NOPRINT N MEAN STDDEV SKEW KURT MAX P99 P95 P90 P75 P50 P25 P10 P5 P1 MIN VARDEF = N DATA = CLEAN_&STUDY._&assessment.;
VAR &varlist.;
CLASS MFC;
WHERE CURRENT_GRADE_LEVEL = "&CG.";
OUTPUT OUT = ALL_STATS_&STUDY.&I. N= MEAN= STDDEV= SKEW= KURT= MAX= P99= P95= P90= P75= P50= P25= P10= P5= P1= MIN= /AUTONAME;
RUN;

DATA ALL_STATS_&STUDY.&I. NOPRINT;
SET ALL_STATS_&STUDY.&I.;
IF MFC = '' THEN DELETE;
RUN;

PROC TRANSPOSE DATA = ALL_STATS_&STUDY.&I. OUT =TEST2&I. ;
ID MFC;
IDLABEL MFC;
RUN;

DATA &SCORE.&I.;
SET TEST2&I.;
_NAME_ = COMPRESS(TRANWRD(UPCASE(_NAME_), "&SCORE._",""));
RENAME _NAME_ = &score.;
LABEL _NAME_ = &score.;
WHERE UPCASE(_NAME_) CONTAINS "&SCORE.";
RUN;
       %END;

ods tagsets.ExcelXP options(sheet_interval = 'NONE' sheet_name = "&score.");
title1 'ALL GRADE LEVELS';
proc report data = &score.;
RUN;

       %DO M = 1 %TO &NG.;
proc sql noprint;
select cgl into :cgl
from grades
where index = &M.;
quit;
%put &SCORE.&M.;

title1 "Where CGLl = &cgl.";
proc report data = &SCORE.&m.;
RUN;
       %END;
ods tagsets.ExcelXP close;

   %end;

%mend;

As I had mentined, the format of the output is exactly what I need; however, I am only getting the last tab of my varlist macro when I need to get 12.

Any ideas or suggestions for improvement are greatly appreciated.

Thank You!

Re: ODS EXCELXP output multiple tables per sheet across multiple sheets

ballardw — Fri, 29 Jan 2016 18:44:01 GMT

Each time your sheet target changes you need to change the sheet name as a minimum

Re: ODS EXCELXP output multiple tables per sheet across multiple sheets

Tom — Fri, 29 Jan 2016 19:12:22 GMT

You should split the first ODS statement into two. Place the part with the FILE= option before the %DO loop. Leave the part that changes the sheetname insdie the loop. Move the ODS ... CLOSE outside of the loop.

Re: ODS EXCELXP output multiple tables per sheet across multiple sheets

CJ19 — Fri, 29 Jan 2016 19:48:00 GMT

Tom,

Thank you a million time over!

Greatly Appreciated,

Chris

topic ODS EXCELXP output multiple tables per sheet across multiple sheets in ODS and Base Reporting

ODS EXCELXP output multiple tables per sheet across multiple sheets

Re: ODS EXCELXP output multiple tables per sheet across multiple sheets

Re: ODS EXCELXP output multiple tables per sheet across multiple sheets

Re: ODS EXCELXP output multiple tables per sheet across multiple sheets