The SAS Output Delivery System and reporting techniques

Excel tagset with missing values. #VALUE!

Reply
Frequent Contributor
Posts: 97

Excel tagset with missing values. #VALUE!

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
SAS Super FREQ
Posts: 8,647

Re: Excel tagset with missing values. #VALUE!

Hi:
Just curious. What happens if you do:
options missing = '0'; and set missing to 0 instead of blank???? Does that make the formula work?

cynthia
Frequent Contributor
Posts: 97

Re: Excel tagset with missing values. #VALUE!

Setting the options missing = '0' does make it work but the output is not readable.

If the values are missing then it makes it more readable to show as missing instead of zero.

Data when exported from SAS to excel changes the format of the cell which returns the #VALUE" error. If we do this directly in excel (without exporting) to add two numbers =B1+C1, where either one is blank and the other one is populated, it works.

And, I cannot change the formulaes because the sheet is signed off and working.
Valued Guide
Posts: 2,154

Re: Excel tagset with missing values. #VALUE!

have a look at the spreadsheet xml before (out of sas) and after you "fix" it (like f2 on the missing cells)
The change in the underlying xml cells content/tags might give you ideas for postprocessing the sas-generated xml, if adapting the tagsets.excelxp template to place the preferred version of null/missing, is unacceptable.
Frequent Contributor
Posts: 97

Re: Excel tagset with missing values. #VALUE!

I have checked the XML and it stored the type as String for missing values (ss:Type="String">)

Tagset version: v1.94

How can tagset return numeric.


John
M

59




Is anyone else experiencing this behaviour?
Valued Guide
Posts: 2,154

Re: Excel tagset with missing values. #VALUE!

either you have to change the tagsets.excelxp logic (the underlying proc template code) which decides missing is string not numeric, or you have to change the xml that is created.
I know which I would find easier......

good luck
peterC
SAS Super FREQ
Posts: 8,647

Re: Excel tagset with missing values. #VALUE!

Hi:
Yet another solution is possible -- sort of a cheat, but possible. You could allow missing to be set to 0, but within PROC REPORT, change the foreground color of the 0 to match the background color -- effectively disappearing the 0.

This is possible because you can change the style of a cell based on the cell values using a CALL DEFINE statement, as shown below. Note that I changed to the SASWEB style because white is an unambiguous color and there's nothing that Excel can do to it, if you are running an Excel with a limited color palette. (Sometimes, if Excel does not have your color in the Excel palette, the color gets changed.)

Also note, that I changed your syntax for the STYLE override on the DEFINE statement to have the correct report area of COLUMN for the TAGATTR override.
[pre]
style(column)=[tagattr='type:Number format:#,##0.00'];
[/pre]

It is not appropriate to apply the TAGATTR to a HEADER cell, so it really is better to specify the COLUMN for the TAGATTR override. PROC REPORT expects you to specify a report area on most STYLE overrides. I tried to explicitly change the type to NUMBER for Excel, but that didn't stop the #VALUE! behavior in the spreadsheet -- it is not hurting, so I left it in.

cynthia
[pre]
Options missing = 0;
* will use 0 and then hide by changing foreground color;

data work.class;
set sashelp.class;
if sex='M' then
weight=.;
run;

ods tagsets.excelxp file="c:\temp\file_changebk.xls"
style=sasweb
options(embedded_titles='yes' sheet_interval='none'
width_fudge='0.75' sheet_name="shoes"
default_column_width="10");

ods tagsets.excelxp options(embedded_titles='yes'
sheet_interval='none' sheet_name="class");
title "Title - Class";

proc report data=work.class nowd nowindows ;
define weight / display
style(column)=[tagattr='type:Number format:#,##0.00'];
define height / display
style(column)=[tagattr='type:Number format:#,##0.00'];
define age / display
style(column)=[tagattr='type:Number format:#,##0.00'];

compute weight;
if weight gt 0 then do;
call define(_col_,'style','style={foreground=black background=white}');
end;
else do;
call define(_col_,'style','style={foreground=white background=white}');
end;
endcomp;
run;
title;

ods tagsets.excelxp close;
ods listing;
[/pre]
Frequent Learner
Posts: 1

Re: Excel tagset with missing values. #VALUE!

Hi,

I found your answer helpful for some scenarios. Thank you!

 

Now I got a new requirement saying 0 and missing(.) have different meaning. I mean 0 means active item and missing(.) means dead item. So, the output excel file from tagsets.excelxp need to have zero's as zero's and missing(.) in sas table to be blank cell(not empty(which is the result of using options missing=' ', as it a string in output file)) in excel file generated thru tagsets. Please help me if there is a way in doing it.

 

Chandra

Contributor
Posts: 40

Re: Excel tagset with missing values. #VALUE!

My less elegant solution would be as follows:

Test C for blank and if not blank, then test B for blank, if not blank then calculate.

=IF(C4=" ", " ", IF(B4=" "," ", IF(A=" "," ",B4-C4)))
Grand Advisor
Posts: 9,335

Re: Excel tagset with missing values. #VALUE!

I am not sure .
Maybe you need System Option ' invaliddata ' which can change invalid numeric data to value you want.

[pre]
options invaliddata=' ';
[/pre]


Ksharp
SAS Super FREQ
Posts: 8,647

Re: Excel tagset with missing values. #VALUE!

But, INVALIDDATA only applies to data that is being READ...not data that is being written. INVALIDDATA is an input control system option.

It says in the doc:
"The INVALIDDATA= system option specifies the value that SAS is to assign to a variable when invalid numeric data is read with an INPUT statement or the INPUT function."

from: http://support.sas.com/documentation/cdl/en/lrdict/63026/HTML/default/viewer.htm#a000210381.htm

The OP issue was with the MISSING=' ' making a subsequent formula in Excel not work as desired. The fact that MISSING=0 caused the formula to work showed that the issue was with Excel not liking the blank in the numeric cell. INVALIDDATA= option won't change how the missing value is displayed in the TAGSETS.EXCELXP report when the XML is opened and rendered with Excel.

cynthia
Grand Advisor
Posts: 9,335

Re: Excel tagset with missing values. #VALUE!

Thx
Post a Question
Discussion Stats
  • 11 replies
  • 1788 views
  • 0 likes
  • 6 in conversation