ods listing close;
Options missing = ' '; * added to hide the periods in the spreadsheet;
ods tagsets.excelxp file="c:\file1.xls"
style=BarrettsBlue
options(embedded_titles='yes' sheet_interval='none'
width_fudge='0.75' sheet_name="shoes"
default_column_width="10,10,10,10,10,10,10,10,10,10,10,10,10,10,10")
;
ods tagsets.excelxp options(embedded_titles='yes'
sheet_interval='none' sheet_name="class");
title "Title - Class";
data work.class;
set sashelp.class;
if sex='M' then
weight=.;
run;
proc report data=work.class nowd nowindows ;
define weight / display style=[tagattr='format:#,##0.00'];
define height / display style=[tagattr='format:#,##0.00'];
define age / display style=[tagattr='format:#,##0.00'];
run;
title;
ods tagsets.excelxp close;
ods listing;
/*****************
The above output will produce an excel file ("file1.xls") with some values of
column weight as blank.
But the format in the cells is format:#,##0.00'
Requirement: (example of what I intend to use is given)
I have another excel file "file2.xls" which is the main excel file having lots of formulaes and calculations based on the first part of the columns.
From file1.xls Name, height and Weight column values are copied and pasted into file2.xls in cells A, B and C respectively.
file2.xls is having formula in cell D as
=IF(A4="", " ",B4-C4)
This should be populated autumatically when the values are pasted in cell A, B and C.
Problem:
The formula calculates the values only where there are no missing values in cell B and C. Where there are missing values in cells B, C the forumla results in #VALUE!
To populate the value I have to go to the cell having missing value, press F2 and then press tab to come out of the cell. It works.
[---------------
FOR THE PURPOSE OF TESTING you could try in the file1.xls itself.
try using a formula as =B1 + C1 and it should result in #VALUE!
----------------]
Please help.
I cannot change the formulas in excel as I am not the owner and its huge.
*********************/
Regards
Sanjay