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