The SAS Output Delivery System and reporting techniques

ODS EXCELXP output multiple tables per sheet across multiple sheets

Accepted Solution Solved
Reply
New Contributor
Posts: 2
Accepted Solution

ODS EXCELXP output multiple tables per sheet across multiple sheets

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!


Accepted Solutions
Solution
‎01-29-2016 02:47 PM
Super User
Super User
Posts: 6,851

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

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.

 

View solution in original post


All Replies
Super User
Posts: 11,144

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

Each time your sheet target changes you need to change the sheet name as a minimum
Solution
‎01-29-2016 02:47 PM
Super User
Super User
Posts: 6,851

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

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.

 

New Contributor
Posts: 2

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

Tom,

 

Thank you a million time over! 

 

Greatly Appreciated,

Chris

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 3 replies
  • 582 views
  • 0 likes
  • 3 in conversation