BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
CJ19
Calcite | Level 5

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!

1 ACCEPTED SOLUTION

Accepted Solutions
Tom
Super User Tom
Super User

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

3 REPLIES 3
ballardw
Super User
Each time your sheet target changes you need to change the sheet name as a minimum
Tom
Super User Tom
Super User

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.

 

CJ19
Calcite | Level 5

Tom,

 

Thank you a million time over! 

 

Greatly Appreciated,

Chris

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

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
  • 3 replies
  • 2012 views
  • 0 likes
  • 3 in conversation