Data visualization with SAS programming

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

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 17
Accepted Solution

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

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;


Accepted Solutions
Solution
‎12-12-2017 08:20 AM
Occasional Contributor
Posts: 17

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

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;

View solution in original post


All Replies
Super Contributor
Super Contributor
Posts: 265

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

 

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.

Occasional Contributor
Posts: 17

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

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;

%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. 

Solution
‎12-12-2017 08:20 AM
Occasional Contributor
Posts: 17

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

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;

☑ This topic is solved.

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

Discussion stats
  • 3 replies
  • 177 views
  • 0 likes
  • 2 in conversation