topic Re: multiple excel sheets in Excel with formulas in SAS Programming

multiple excel sheets in Excel with formulas

jshaik — Tue, 26 Feb 2013 21:03:54 GMT

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.

Re: multiple excel sheets in Excel with formulas

Reeza — Tue, 26 Feb 2013 21:59:05 GMT

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.

Re: multiple excel sheets in Excel with formulas

jshaik — Wed, 27 Feb 2013 14:30:51 GMT

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> Department for Medicaid Services</th>

<th bgcolor='silver' align=left colspan=1>Report Revised :</th>

</tr>

<tr>

<td align=left colspan=19>MCO Report </td></th>

</tr>

<tr>

<td>

</td>

</tr>

<tr>

<td align=left colspan=1>Report Run Date:</td>

<td align=right colspan=15 >Received Date :</td>

</tr>

<tr>

<td align=left colspan=1>Report Period From:</td>

<td align=right colspan=15 >Reviewed Date :</td>

</tr>

<tr>

<td align=left colspan=1>Report Period To:</td>

<td align=right colspan=15 >Reviewer :</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> Department for Medicaid Services</th>

<th bgcolor='silver' align=left colspan=1>Report Revised :</th>

</tr>

<tr>

<td align=left colspan=19>MCO Report </td></th>

</tr>

<tr>

<td>

</td>

</tr>

<tr>

<td align=left colspan=1>Report Run Date:</td>

<td align=right colspan=15 >Received Date :</td>

</tr>

<tr>

<td align=left colspan=1>Report Period From:</td>

<td align=right colspan=15 >Reviewed Date :</td>

</tr>

<tr>

<td align=left colspan=1>Report Period To:</td>

<td align=right colspan=15 >Reviewer :</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 ;

Re: multiple excel sheets in Excel with formulas

Reeza — Wed, 27 Feb 2013 15:43:24 GMT

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

Re: multiple excel sheets in Excel with formulas

jshaik — Wed, 27 Feb 2013 16:05:44 GMT

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.

Re: multiple excel sheets in Excel with formulas

Reeza — Wed, 27 Feb 2013 16:11:17 GMT

Yes it does. Why is that an issue?

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

Re: multiple excel sheets in Excel with formulas

jshaik — Wed, 27 Feb 2013 16:49:22 GMT

Reeza,

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

Re: multiple excel sheets in Excel with formulas

Reeza — Wed, 27 Feb 2013 16:55:29 GMT

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.

Re: multiple excel sheets in Excel with formulas

jshaik — Wed, 27 Feb 2013 19:18:37 GMT

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.