ODS Excel XP Multiple Worksheets

Reply
Regular Contributor
Posts: 222

ODS Excel XP Multiple Worksheets

Hi.....I am trying to create an excel file with multiple worksheets. The data comes from 2 sas datasets (datasets with the names Duplicates and Claims). Everything seems to work fine except that the excel file has worksheets with names Summary, Summary2, Summary3, Summary4, etc. rather than the first worksheet should have the name of Summary followed by P00, P001, P002, etc which are listed under the bygroup variable Pharm_num. I think I might be missing something....any help or suggestions is greatly appreciated....thanks.

ods _all_ close;


options LeftMargin = .5in
   RightMargin = .5in
   TopMargin =.5in
   BottomMargin =.5in;
ods noresults;
ods tagsets.excelXP file="U:\requests\&FILENAME Report (&rundate).xls";
ods tagsets.excelXP
options(sheet_name='Summary' absolute_column_width='6,6' embedded_titles='yes'
  orientation='Landscape' sheet_label=' ' sheet_interval ='none' autofit_height='yes' pages_fitwidth='1' fittopage='yes');

proc report data=DUPLICATES nowd
style(header)={background=lightgrey font_face=Calibri
  font_size=12pt borderwidth=1px bordercolor=black just=center}
style(column)={background=white font_face=Calibri font_size=10pt borderwidth=1px
  bordercolor=black just=center};

column ("Duplicated Claims Summary Report for the &FROMDATE - &TODATE Period" PHARM_NUM FREQUENCY);

      define PHARM_NUM / display 'Pharmacy Number' style(column)={tagattr='0000'};
   define FREQUENCY / display 'Number of Duplicates' Analysis Sum;

   rbreak after / Summarize;

   compute after;
    PHARM_NUM = 'Grand Total';
   endcomp;

     compute PHARM_NUM;
    Row+1;
    if (mod(Row,2)ne 0)
    then call define(_row_,'style','style=[background=lightgrey]');
   endcomp;
run;
quit;

ods tagsets.excelXP
options(absolute_column_width='6,6,8,13,7,7,7,7,10,10,10' embedded_titles='yes'
   orientation='Landscape' sheet_label=' ' suppress_bylines='yes' sheet_interval='bygroup' autofit_height='yes' pages_fitwidth='1' fittopage='yes');

proc report data=CLAIMS nowd
style(header)={background=lightgrey font_face=Calibri
  font_size=12pt borderwidth=1px bordercolor=black just=center}
style(column)={background=white font_face=Calibri font_size=10pt borderwidth=1px
  bordercolor=black just=center};

column ("Duplicated Claims Report for the &FROMDATE - &TODATE Period"  CARRIER_ID CLAIM_DATE PROV_ID_CODE
    PROD_G DIN INGRED_COST_PAID PROF_FEE_PAID TOTAL_AMT_PAID);
   by PHARM_NUM;

define CARRIER_ID / display 'Carrier ID';
define CLAIM_DATE / display 'Claim Date' style(column)={tagattr='00000000'};
define PROV_ID_CODE / display 'Prov ID Code';
define PROD_G / display 'Name';
define PIN / display 'PIN' style(column)={tagattr='00000000'};
  define INGRED_COST_PAID / display 'Ingredient Cost' style(column)={tagattr='format:$#,##0.00'};
define PROF_FEE_PAID / display 'Dispensing Fee' style(column)={tagattr='format:$#,##0.00'};
define TOTAL_AMT_PAID / display 'Total Paid' style(column)={tagattr='format:$#,##0.00'};


define prov_id_code / display;
  compute prov_id_code;
   if (din ne lag(din)) then count=0;
   if ((prov_id_code ne lag(prov_id_code)) OR (claim_date ne lag(claim_date))) then count+1;
  if mod(count,2)=0 then call define(_row_,"style","style={background=lightgrey}");
endcomp;
run;
quit;

ods tagsets.excelXP close;

Super User
Posts: 17,868

Re: ODS Excel XP Multiple Worksheets

Post the log.

Super User
Posts: 10,516

Re: ODS Excel XP Multiple Worksheets

Your first out put is generated with sheet_interval='none', you might want 'bygroup' as you have for the second.

Ask a Question
Discussion stats
  • 2 replies
  • 220 views
  • 0 likes
  • 3 in conversation