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

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;

1 ACCEPTED SOLUTION

Accepted Solutions
Char
Obsidian | Level 7

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

3 REPLIES 3
HB
Barite | Level 11 HB
Barite | Level 11

 

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.

Char
Obsidian | Level 7

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. 

Char
Obsidian | Level 7

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;

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
  • 806 views
  • 0 likes
  • 2 in conversation