topic ODS ExcelXP - PROC Report varying number of tables per excel sheet in Graphics Programming

ODS ExcelXP - PROC Report varying number of tables per excel sheet

Char — Wed, 06 Dec 2017 19:35:49 GMT

Hello!

I am being requested to create a workbook that contains multiple tabs and a varying number of reports on each tab.

This is my sample code which produces a table on 2 different tabs where the name of the 2nd tab just has a (2) appended to it. I'd actually like the 2 reports to show up on the same tab. Is that possible? Any help is appreciated. Thanks!

%MACRO BYLCD(LCDDESC,ITERS,LCD);

ODS LISTING CLOSE;

Ods tagsets.ExcelXP style=XLsansPrinter options(sheet_name="&LCD." embedded_titles='yes')
FILE = "&DIR.\XLS\20&CY. JH Auto Routine Edit Effectiveness Tracker A-L.xml";

%DO I = 1 %TO &ITERS;

PROC REPORT DATA= HA_T1 NOWD SPLIT="!";
WHERE LCD_DESCRIPTION = "&LCDDESC." AND X = &I;
COLUMN EDIT ACTIVE_DATE QTR CLAIMS_REVIEWED CLAIMS_DENIED PCT_CLAIMS_DENIED
CHARGES_REVIEWED CHARGES_DENIED PCT_CHARGES_DENIED
Avg_Dol_Saved_per_Claim_Reviewed;

TITLE1 "&LCD. - &LCDDESC.";
TITLE4 'PART A';

   DEFINE EDIT                             / 'EDIT' style(column)={tagattr='format:@' just=c};
   DEFINE ACTIVE_DATE                      /         style(column)={tagattr='format:@' just=c};
   DEFINE QTR                              /         style(column)={tagattr='format:@' just=c};
   DEFINE CLAIMS_REVIEWED                  /         style(column)={tagattr='#,##0'     just=c};
   DEFINE CLAIMS_DENIED                    /         style(column)={tagattr='#,##0'     just=c};
   DEFINE PCT_CLAIMS_DENIED                /         style(column)={tagattr='##0.0%'    just=c};
   DEFINE CHARGES_REVIEWED                 /         style(column)={tagattr='$#,##0.00' just=c};
   DEFINE CHARGES_DENIED                   /         style(column)={tagattr='$#,##0.00' just=c};
   DEFINE PCT_CHARGES_DENIED               /         style(column)={tagattr='##0.0%'    just=c};
   DEFINE Avg_Dol_Saved_per_Claim_Reviewed /         style(column)={tagattr='$#,##0.00' just=c};
run;

%END;

ODS tagsets.ExcelXP CLOSE;
ODS LISTING;

%mend BYLCD;

data _null_;
set /*ALLHLCDSAL*/ TEST;
BY LCD_DESCRIPTION;
call execute('%BYLCD('||LCD_DESCRIPTION||','||X||','||LCD||')');
run;
quit;

Re: ODS ExcelXP - PROC Report varying number of tables per excel sheet

HB — Wed, 06 Dec 2017 20:21:59 GMT

http://support.sas.com/kb/40/264.html

For example, if you want to display a table and a graph on the same page, the PANELCOLS= option can be used to add multiple procedures on the same page. Using the reserved tab name HIDE allows you to place multiple procedures under a single tab. In the example below, the output of the SASHELP.CLASS and the SASHELP.AIR data sets share the same tab.E

They use

ods tagsets.tableeditor file="c:\temp\temp.html" style=sasweb

options(panelcols="2" web_tabs="SASHELP.CLASS/AIR,HIDE,SASHELP.ORSALES");

So maybe try something like

Ods tagsets.ExcelXP style=XLsansPrinter options(sheet_name="&LCD." embedded_titles='yes' panelcols="2" )
FILE = "&DIR.\XLS\20&CY. JH Auto Routine Edit Effectiveness Tracker A-L.xml";

and see what happens.

Re: ODS ExcelXP - PROC Report varying number of tables per excel sheet

Char — Mon, 11 Dec 2017 18:08:38 GMT

Thank you for the try! Panelcols unfortunately did not work but sheet_interval='none' did have some effect.

When I did this...

Ods tagsets.ExcelXP style=XLsansPrinter options(sheet_name="&LCD." embedded_titles='yes' sheet_interval='none')

FILE = "&DIR.\XLS\20&CY. JH Auto Routine Edit Effectiveness Tracker A-L.xml";

...my output did all show up on one tab. This is good but I want a combination of varying tables going down a tab, but also producing multiple tabs. I can seem to get it to do one or the other, but not both. I tried iteratively switching between using the sheet interval and not using the sheet interval option but that did not work as I'd hoped...

ODS LISTING CLOSE;

%DO I = 1 %TO &ITERS;

%IF I = 1 %THEN %DO;

Ods tagsets.ExcelXP style=XLsansPrinter options(sheet_name="&LCD." embedded_titles='yes')

FILE = "&DIR.\XLS\20&CY. JH Auto Routine Edit Effectiveness Tracker A-L.xml";

PROC REPORT DATA= HA_T1 NOWD SPLIT="!";

WHERE LCD_DESCRIPTION = "&LCDDESC." AND X = &I;

COLUMN EDIT ACTIVE_DATE QTR CLAIMS_REVIEWED CLAIMS_DENIED PCT_CLAIMS_DENIED

CHARGES_REVIEWED CHARGES_DENIED PCT_CHARGES_DENIED

Avg_Dol_Saved_per_Claim_Reviewed;

TITLE1 "&LCD. - &LCDDESC.";

TITLE4 'PART A';

DEFINE EDIT / 'EDIT' style(column)={tagattr='format:@' just=c};

DEFINE ACTIVE_DATE / style(column)={tagattr='format:@' just=c};

DEFINE QTR / style(column)={tagattr='format:@' just=c};

DEFINE CLAIMS_REVIEWED / style(column)={tagattr='#,##0' just=c};

DEFINE CLAIMS_DENIED / style(column)={tagattr='#,##0' just=c};

DEFINE PCT_CLAIMS_DENIED / style(column)={tagattr='##0.0%' just=c};

DEFINE CHARGES_REVIEWED / style(column)={tagattr='$#,##0.00' just=c};

DEFINE CHARGES_DENIED / style(column)={tagattr='$#,##0.00' just=c};

DEFINE PCT_CHARGES_DENIED / style(column)={tagattr='##0.0%' just=c};

DEFINE Avg_Dol_Saved_per_Claim_Reviewed / style(column)={tagattr='$#,##0.00' just=c};

run;

%END;

%IF I > 1 %THEN %DO;

Ods tagsets.ExcelXP style=XLsansPrinter options(sheet_name="&LCD." embedded_titles='yes' sheet_interval='none')

FILE = "&DIR.\XLS\20&CY. JH Auto Routine Edit Effectiveness Tracker A-L.xml";

PROC REPORT DATA= HA_T1 NOWD SPLIT="!";

WHERE LCD_DESCRIPTION = "&LCDDESC." AND X = &I;

COLUMN EDIT ACTIVE_DATE QTR CLAIMS_REVIEWED CLAIMS_DENIED PCT_CLAIMS_DENIED

CHARGES_REVIEWED CHARGES_DENIED PCT_CHARGES_DENIED

Avg_Dol_Saved_per_Claim_Reviewed;

TITLE1 "&LCD. - &LCDDESC.";

TITLE4 'PART A';

DEFINE EDIT / 'EDIT' style(column)={tagattr='format:@' just=c};

DEFINE ACTIVE_DATE / style(column)={tagattr='format:@' just=c};

DEFINE QTR / style(column)={tagattr='format:@' just=c};

DEFINE CLAIMS_REVIEWED / style(column)={tagattr='#,##0' just=c};

DEFINE CLAIMS_DENIED / style(column)={tagattr='#,##0' just=c};

DEFINE PCT_CLAIMS_DENIED / style(column)={tagattr='##0.0%' just=c};

DEFINE CHARGES_REVIEWED / style(column)={tagattr='$#,##0.00' just=c};

DEFINE CHARGES_DENIED / style(column)={tagattr='$#,##0.00' just=c};

DEFINE PCT_CHARGES_DENIED / style(column)={tagattr='##0.0%' just=c};

DEFINE Avg_Dol_Saved_per_Claim_Reviewed / style(column)={tagattr='$#,##0.00' just=c};

run;

%END;

ODS tagsets.ExcelXP CLOSE;

ODS LISTING;

%mend BYLCD;

data _null_;

set /*ALLHLCDSAL*/ TEST;

BY LCD_DESCRIPTION;

call execute('%BYLCD('||LCD_DESCRIPTION||','||X||','||LCD||')');

run;

quit;

I'm hoping to get one tab per LCD, w/ multiple tables per tab. The above code gets me only one tab - continuously overwriting the LCD and leaving me with all the tables from the last LCD on that one tab.

Re: ODS ExcelXP - PROC Report varying number of tables per excel sheet

Char — Tue, 12 Dec 2017 13:18:24 GMT

I finally figured it out! I just had to move my file statement out of the macro - otherwise I was just overwriting my first tab w/ the 2nd etc. (duh). Thanks for trying though!

ODS LISTING CLOSE;

Ods tagsets.ExcelXP style=XLsansPrinter

FILE = "&DIR.\XLS\20&CY. JH Auto Routine Edit Effectiveness Tracker A-L.xml";

%MACRO BYLCD(LCDDESC,ITERS,LCD);

Ods tagsets.ExcelXP style=XLsansPrinter options(sheet_name="&LCD." embedded_titles='yes' sheet_interval="none");

%DO I = 1 %TO &ITERS;

PROC REPORT DATA= HA_T1 NOWD SPLIT="!";

WHERE LCD_DESCRIPTION = "&LCDDESC." AND X = &I;

COLUMN EDIT ACTIVE_DATE QTR CLAIMS_REVIEWED CLAIMS_DENIED PCT_CLAIMS_DENIED

CHARGES_REVIEWED CHARGES_DENIED PCT_CHARGES_DENIED

Avg_Dol_Saved_per_Claim_Reviewed;

TITLE1 "&LCD. - &LCDDESC.";

TITLE4 'PART A';

DEFINE EDIT / 'EDIT' style(column)={tagattr='format:@' just=c};

DEFINE ACTIVE_DATE / style(column)={tagattr='format:@' just=c};

DEFINE QTR / style(column)={tagattr='format:@' just=c};

DEFINE CLAIMS_REVIEWED / style(column)={tagattr='#,##0' just=c};

DEFINE CLAIMS_DENIED / style(column)={tagattr='#,##0' just=c};

DEFINE PCT_CLAIMS_DENIED / style(column)={tagattr='##0.0%' just=c};

DEFINE CHARGES_REVIEWED / style(column)={tagattr='$#,##0.00' just=c};

DEFINE CHARGES_DENIED / style(column)={tagattr='$#,##0.00' just=c};

DEFINE PCT_CHARGES_DENIED / style(column)={tagattr='##0.0%' just=c};

DEFINE Avg_Dol_Saved_per_Claim_Reviewed / style(column)={tagattr='$#,##0.00' just=c};

run;

%END;

%mend BYLCD;

data _null_;

set /*ALLHLCDSAL*/ TEST;

BY LCD_DESCRIPTION;

call execute('%BYLCD('||LCD_DESCRIPTION||','||X||','||LCD||')');

run;

quit;

ODS tagsets.ExcelXP CLOSE;

ODS LISTING;