multiple excel sheets in Excel with formulas

[jshaik]

Hi,

I am working on a report that has two sheets in an excel file.requirement is as below.

sheet1 has all the detailed information

sheet2 has % , averages and sums etc but it takes input from sheet1. Instead of logic,I was given excel formulas (like  =IFERROR(AVERAGE(C12:O12),0)) to calculate the fields in sheet2.

If a value in sheet1 is changed,it should reflect in the % ,averages in the sheet2.

In the below example,if in sheet1 rxs value for jan12 is changed, rxs and %rxs for jan12 in sheet2 should change as well.

For example:

sheet1:                                             sheet2 :

var     jan12    jan13                         var     jan12    jan13

rxs    10         20                             rxs     10        20

refills  20         30                            %rxs   33.3%  %66.6

I have tried below approaches but in vain..

Approach 1 :

MSOFFICE2K

MSOFFICE2K_X   

---- can't create multiple sheets.so created each sheet seperately using msoffice2k and combined them using msoffice2k_x .But this results in sheet2 with no values as it loses reference to sheet1.More over,since I am working on Unix SAS,I am not sure how to refer to unix path.I have used below code which is not working.

ods tagsets.msoffice2k_x file="/reporting/rep_final.xls" style=journal

      options( worksheet_source=" Data Entry # '/reporting/rep_sheet1.xls', Summary #'/reporting/rep_sheet2.xls' "

               );

           data _null_;                                                                                                                         

           file print;                                                                                                                        

           put _all_;                                                                                                                         

run;

ods tagsets.msoffice2k_x close;

ods _all_ close ;

ods listing ;

Approach2:

I have tried creating it with ExcelXp tagset but it is not recognizing the excel formulas in =IFERROR(AVERAGE(C12:O12),0) format.

My sas environment details:

SAS version : sas 9.1.3 on UNIX (solaris)

msoffice2k_x version : 2.1

Attached screenshot shows dataset1 (for sheet1) with detailed info.whereas the dataset2 (for sheet2) with only excel formulas.

Please suggest a solution to create an excel workbook with two sheets one sheet referring other to pull data.Thanks in advance.

[Reeza]

Three options:

Tagsets does allow you to build formula's but you do need to set an option so you can post what you tried and someone can help you debug it.

You could implement the formulas in SAS, so Excel is only used to present the data.

Build an excel template first and export data to sheet one and then sheet 2 will be automatically updated.

[jshaik]

Hi Reeza,

below is my code.

Screenshot1 : data_entry_sheet_final dataset

Screenshot2 : data_summary_final_summary dataset

Options noQuoteLenMax;

  ods listing close;

  ods tagsets.msoffice2k file="reporting/report39_sheet1.xls"    style=normal

  options (fittopage='yes' EMBEDDED_TITLES = 'YES' Center_Horizontal = 'yes' Center_Vertical='yes');

  proc report data=data_entry_sheet_final nowd split= '*' STYLE(header)=[BACKGROUND = silver foreground=black font_size=3 font_face=Calibri ];

  title1 "<table border ='1'>

  <tr>

  <th align=left colspan=16><font size=3 face=calibri><b> Department for Medicaid Services</b></font></th>

  <th bgcolor='silver' align=left colspan=1><font size=2.5 face=calibri>Report Revised :</font></th>

  <th colspan = 2><font size=2.5 face=calibri></font></th>

  </tr>

  <tr>

  <td align=left colspan=19><font size=3 face=calibri><b>MCO Report </b></font></td></th>

  </tr>     

  <tr>

  <td align=left colspan=1><font size=3 face=calibri>MCO Name:</font></td>

  <td>

  </td>

  <td align=right colspan=15><font size=3 face=calibri>DMS Use Only</font></td>

  </tr> 

  <tr>

  <td align=left colspan=1><font size=2.5 face=calibri>Report Run Date:</font></td>

  <td <font size=3 face=calibri>&rundate.</font></td>

  <td align=right colspan=15 ><font size=2.5 face=calibri>Received Date :</font></td>

  <td align=right colspan=2></td>

  </tr>

  <tr>

  <td align=left colspan=1><font size=2.5 face=calibri>Report Period From:</font></td>

  <td <font size=3 face=calibri>&begdt.</font></td>

  <td align=right colspan=15 ><font size=2.5 face=calibri>Reviewed Date :</font></td>

  <td align=right colspan=2></td>

  </tr>

  <tr>

  <td align=left colspan=1><font size=2.5 face=calibri>Report Period To:</font></td>

  <td <font size=3 face=calibri>&enddt.</font></td>

  <td align=right colspan=15 ><font size=2.5 face=calibri>Reviewer :</font></td>

  <td align=right colspan=2></td>

  </tr>

  <tr>

  </tr>

  <tr>

  </tr>

  <tr>

  </tr>

  </table>";

  footnote;

  column 

  VAR0

  VAR   

                 &mth1 

                 &mth2 

                 &mth3 

                 &mth4 

                 &mth5 

                 &mth6 

                 &mth7 

                 &mth8 

                 &mth9 

                 &mth10

                 &mth11

                 &mth12

                 &mth13

                 CHANGE_PER_MONTH

                 CHANGE_PER_YEAR

                 AVERAGE_PER_MONTH

                  Y_T_D 

  ;

  define var0                    /display  ' '                   ;

  define VAR     /display ' ' ;

  define &mth1   /display ;

  define &mth2   /display ;

  define &mth3   /display ;

  define &mth4   /display ;

  define &mth5   /display ;

  define &mth6   /display ;

  define &mth7   /display ;

  define &mth8   /display ;

  define &mth9   /display ;

  define &mth10 /display ;

  define &mth11 /display ;

  define &mth12 /display ;

  define &mth13 /display ;

  define CHANGE_PER_MONTH /display '% CHANGE_PER_MONTH' ;

  define CHANGE_PER_YEAR /display '% CHANGE_PER_YEAR' ;

  define AVERAGE_PER_MONTH /display 'AVERAGE_PER_MONTH' ;

  define Y_T_D /display 'Y_T_D' ;

  run;

  ods tagsets.msoffice2k close;

  ods listing;

Options noQuoteLenMax;

  ods listing close;

  ods tagsets.msoffice2k file="reporting/report39_sheet2.xls"    style=normal

  options (fittopage='yes' EMBEDDED_TITLES = 'YES' Center_Horizontal = 'yes' Center_Vertical='yes');

  proc report data=Data_summary_sheet_final nowd split= '*' STYLE(header)=[BACKGROUND = silver foreground=black font_size=3 font_face=Calibri ];

  title1 "<table border ='1'>

  <tr>

  <th align=left colspan=16><font size=3 face=calibri><b> Department for Medicaid Services</b></font></th>

  <th bgcolor='silver' align=left colspan=1><font size=2.5 face=calibri>Report Revised :</font></th>

  <th colspan = 2><font size=2.5 face=calibri></font></th>

  </tr>

  <tr>

  <td align=left colspan=19><font size=3 face=calibri><b>MCO Report </b></font></td></th>

  </tr>     

  <tr>

  <td align=left colspan=1><font size=3 face=calibri>MCO Name:</font></td>

  <td>

  </td>

  <td align=right colspan=15><font size=3 face=calibri>DMS Use Only</font></td>

  </tr> 

  <tr>

  <td align=left colspan=1><font size=2.5 face=calibri>Report Run Date:</font></td>

  <td <font size=3 face=calibri>&rundate.</font></td>

  <td align=right colspan=15 ><font size=2.5 face=calibri>Received Date :</font></td>

  <td align=right colspan=2></td>

  </tr>

  <tr>

  <td align=left colspan=1><font size=2.5 face=calibri>Report Period From:</font></td>

  <td <font size=3 face=calibri>&begdt.</font></td>

  <td align=right colspan=15 ><font size=2.5 face=calibri>Reviewed Date :</font></td>

  <td align=right colspan=2></td>

  </tr>

  <tr>

  <td align=left colspan=1><font size=2.5 face=calibri>Report Period To:</font></td>

  <td <font size=3 face=calibri>&enddt.</font></td>

  <td align=right colspan=15 ><font size=2.5 face=calibri>Reviewer :</font></td>

  <td align=right colspan=2></td>

  </tr>

  <tr>

  </tr>

  <tr>

  </tr>

  <tr>

  </tr>

  </table>";

  footnote;

  column 

  VAR0

  VAR   

                 &mth1 

                 &mth2 

                 &mth3 

                 &mth4 

                 &mth5 

                 &mth6 

                 &mth7 

                 &mth8 

                 &mth9 

                 &mth10

                 &mth11

                 &mth12

                 &mth13

                 CHANGE_PER_MONTH

                 CHANGE_PER_YEAR

                 AVERAGE_PER_MONTH

                  Y_T_D 

  ;

  define var0                    /display  ' '                   ;

  define VAR     /display  ' ' ;

  define &mth1   /display ;

  define &mth2   /display ;

  define &mth3   /display ;

  define &mth4   /display ;

  define &mth5   /display ;

  define &mth6   /display ;

  define &mth7   /display ;

  define &mth8   /display ;

  define &mth9   /display ;

  define &mth10 /display ;

  define &mth11 /display ;

  define &mth12 /display ;

  define &mth13 /display ;

  define CHANGE_PER_MONTH /display '% CHANGE_PER_MONTH' ;

  define CHANGE_PER_YEAR /display '% CHANGE_PER_YEAR' ;

  define AVERAGE_PER_MONTH /display 'AVERAGE_PER_MONTH' ;

  define Y_T_D /display 'Y_T_D' ;

  run;

  ods tagsets.msoffice2k close;

  ods listing;

ods tagsets.msoffice2k_x file="reporting/report39_final.xls" style=journal

      options( worksheet_source=" Data Entry # 'reporting/report39_sheet1.xls',

                                  Summary #'reporting/report39_sheet2.xls' "

               );

        data _null_;                                                                                                                         

           file print;                                                                                                                        

           put _all_;                                                                                                                         

run;

ods tagsets.msoffice2k_x close;

ods _all_ close ;

ods listing ;

[Reeza]

Sorry, I meant tagsets.excelxp allows formula's. I'm not familiar with MSOffice tagsets.

[jshaik]

Reeza,

I believe ExcelXp tagset does not recognize formulas in , for example , =IFERROR(AVERAGE(C30:O30),0)  format. Instead, it recognizes formulas in RC[-1]-RC[-2] format.Thanks.

[Reeza · Accepted solution]

Yes it does. Why is that an issue?

http://www.sascommunity.org/wiki/Creating_Summary_and_Detail_Sections_in_an_Excel_Worksheet_Using_th...

[jshaik]

Reeza,

I think ExcelXP accepts only R1C1 notation rather than A1. This snippet is from the link you provided.

[Reeza]

Yes, but why can't you convert the formula to RC formulas?

If you place the formula in excel as provided and then switch it to RC formula as noted in the link it would work.

There's always DDE I suppose or one of my two other initial suggestions.

Good Luck.

[jshaik]

Reeza,

Thanks for the tip.I converted all my excel formulas in A1 notation to R1C1 notation and with ExcelXP created the report as needed.Thanks a lot.