multiple excel sheets in Excel with formulas

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 14
Accepted Solution

multiple excel sheets in Excel with formulas

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(C12Smiley Surprised12),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(C12Smiley Surprised12),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.


Screenshot.png

Accepted Solutions
Solution
‎02-27-2013 11:11 AM
Super User
Posts: 17,784

Re: multiple excel sheets in Excel with formulas


All Replies
Super User
Posts: 17,784

Re: multiple excel sheets in Excel with formulas

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.

Occasional Contributor
Posts: 14

Re: multiple excel sheets in Excel with formulas

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 ;

data_entry_final_sheet.png

Data_summary_final_sheet.png

Super User
Posts: 17,784

Re: multiple excel sheets in Excel with formulas

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

Occasional Contributor
Posts: 14

Re: multiple excel sheets in Excel with formulas

Reeza,

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

Solution
‎02-27-2013 11:11 AM
Super User
Posts: 17,784

Re: multiple excel sheets in Excel with formulas

Occasional Contributor
Posts: 14

Re: multiple excel sheets in Excel with formulas

Reeza,

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

excelxp_formula.png

Super User
Posts: 17,784

Re: multiple excel sheets in Excel with formulas

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.

Occasional Contributor
Posts: 14

Re: multiple excel sheets in Excel with formulas

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.

☑ This topic is SOLVED.

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

Discussion stats
  • 8 replies
  • 553 views
  • 0 likes
  • 2 in conversation