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!
... View more
