proc report to ods excel

RENATA1 — Fri, 19 Oct 2018 15:19:31 GMT

Hi there!

I need to create a report that:

produces data across based on TIMEFRAME field (ex: 2017q1, 2017q2… 2018q3 – a two year lookup that changes each time the report is run)
Creates a new excel tab for each combination of GROUP_NAME STATUS REPORTING_STATUS values (by value 2, 3, 4)
Has different sections per each tab; where MEASURE column changes depending on the CATEGORY it is under; it can represent dollars or counts.

As of now, I’m trying to add each of those section using a new ‘proc report’ statement. However, because I have SHEET_INTERVAL='#BYVAL5', the second proc report creates new tabs for each #byvalue with number 2 added to a sheet name.

If I switch to SHEET_INTERVAL='none', I lose all but one tab; all the data is outputted on a single sheet for both reports.

Is there a way to combine crating different sheets, yet being able to have multiple proc reports (different sections) under each of those sheets? Please advise! Thank you!

Sample of the desirable output below.

ODS EXCEL FILE=&OUTFILE01.

OPTIONS(   EMBEDDED_TITLES='YES'

           EMBED_TITLES_ONCE= 'YES'

           EMBEDDED_FOOTNOTES = 'YES'

           START_AT='1,1'

           GRIDLINES='ON'

           ORIENTATION = 'LANDSCAPE'

           FROZEN_HEADERS='YES'

           FROZEN_ROWHEADERS = 'NO'

           TITLE_FOOTNOTE_NOBREAK='YES'

           SHEET_NAME = '#BYVAL5'

           SHEET_INTERVAL='#BYVAL5'

           SHEET_LABEL = ' '

           );

TITLE j=l 'ABC Corporation';

TITLE2 j=l 'Time Period: ' &BEGIN_DT. ' - ' &END_DT.;

TITLE3 J=L '#BYVAL2 #BYVAL3';

TITLE4 J=L '#BYVAL4';

TITLE5 J=LEFT ' ';

TITLE6 J=L 'Paid Claims Per Participant Per Month by Service';

 

PROC REPORT DATA=WORK.RPT1SEC1 NOWD HEADLINE HEADSKIP SPLIT= '*';

BY KEYCOL GROUP_NAME STATUS REPORTING_STATUS SHEETNM;

COLUMN KEYCOL REC CATEGORY TIMEFRAME,(' ' MEASURE PPPM DUMMY);

DEFINE KEYCOL / GROUP PAGE NOPRINT;

DEFINE REC / GROUP NOPRINT;

DEFINE CATEGORY / GROUP   ' ' LEFT

           STYLE(COLUMN)={JUST=L TAGATTR='WRAPTEXT:NO' WIDTH=100% CELLWIDTH=1.50in};

DEFINE TIMEFRAME / ACROSS ' ' ORDER=DATA LEFT

           STYLE(COLUMN)={JUST=L TAGATTR='WRAPTEXT:NO' WIDTH=100%};

DEFINE MEASURE / ANALYSIS 'Dollars'

           STYLE(COLUMN)={JUST=R TAGATTR='FORMAT:$#,##0.00_);[RED]\($#,##0.00\)' CELLWIDTH=1.00in};

DEFINE PPPM / ANALYSIS 'PPPM'

           STYLE(COLUMN)={JUST=R TAGATTR='FORMAT:$#,##0.00_);[RED]\($#,##0.00\)' CELLWIDTH=1.00in};

DEFINE DUMMY / COMPUTED NOZERO ' '

           STYLE(COLUMN)={JUST=R COLOR=WHITE TAGATTR='FORMAT:#,##0.00_);[RED]\(#,##0.00\)' CELLWIDTH=0.25in};

RBREAK AFTER / SUMMARIZE;

 

COMPUTE DUMMY;

DUMMY = .;

ENDCOMP;

 

COMPUTE AFTER KEYCOL;

LINE ' ';

ENDCOMP;

 

COMPUTE AFTER;

CATEGORY="Total";

LINE ' ';

ENDCOMP;

RUN;

 

PROC REPORT DATA=WORK.RPT1SEC2 NOWD NOHEADER SPLIT= '*';

BY KEYCOL GROUP_NAME STATUS REPORTING_STATUS SHEETNM;

COLUMN KEYCOL REC CATEGORY TIMEFRAME,(' ' DUMMY1 MEASURE DUMMY2);

DEFINE KEYCOL / GROUP PAGE NOPRINT;

DEFINE REC / GROUP NOPRINT;

DEFINE CATEGORY / GROUP   ' ' LEFT

           STYLE(COLUMN)={JUST=L TAGATTR='WRAPTEXT:NO' WIDTH=100% CELLWIDTH=1.50in};

DEFINE TIMEFRAME / ACROSS ' ' ORDER=DATA LEFT

STYLE(COLUMN)={JUST=L TAGATTR='WRAPTEXT:NO' WIDTH=100%};

DEFINE DUMMY1 / COMPUTED NOZERO ' '

           STYLE(COLUMN)={JUST=R COLOR=WHITE TAGATTR='FORMAT:#,##0.00_);[RED]\(#,##0.00\)' CELLWIDTH=1.00in};

DEFINE MEASURE / ANALYSIS ' '

           STYLE(COLUMN)={JUST=R TAGATTR='FORMAT:#,##0.00_);[RED]\(#,##0.00\)' CELLWIDTH=1.00in};

DEFINE DUMMY2 / COMPUTED NOZERO ' '

           STYLE(COLUMN)={JUST=R COLOR=WHITE TAGATTR='FORMAT:#,##0.00_);[RED]\(#,##0.00\)' CELLWIDTH=0.25in};

 

COMPUTE DUMMY1;

DUMMY1 = .;

ENDCOMP;

 

COMPUTE DUMMY2;

DUMMY2 = .;

ENDCOMP;

 

COMPUTE AFTER KEYCOL;

LINE ' ';

ENDCOMP;

RUN;

 

ODS EXCEL CLOSE

Re: proc report to ods excel

Reeza — Fri, 19 Oct 2018 15:27:08 GMT

You can change your options when needed.

ods excel file='c:\_localdata\demo.xlsx' style=statistical;
*keeps both on the same sheet;
ods excel options (sheet_interval='none');

proc means data=sashelp.class;
run;

proc freq data=sashelp.class;
table age;
run;

*changes settings to have one sheet per by group;
ods excel options(sheet_interval='bygroup');

proc sort data=sashelp.class out=class;
by sex;

proc reg data=class;
by sex;
model weight = height age;
run;

*starts a new sheet now;
ods excel options (sheet_interval='now');
proc means data=sashelp.cars;
var mpg_city mpg_highway;
run;

ods excel close;

@RENATA1 wrote: