topic Re: special missing values in ODS and Base Reporting

special missing values

christa — Thu, 03 Apr 2014 13:54:39 GMT

I am using special missing values to differentiate between different "nothings" in a numeric column.

Is there a way to get these to excel via ods tagsets.excelxp?

Re: special missing values

RW9 — Thu, 03 Apr 2014 14:29:25 GMT

Could you clarify with an example? Your proc report within the ods tageset open/close should print all text out, however its not a given that what Excel displays is the same as witihin SAS.

Re: special missing values

christa — Thu, 03 Apr 2014 14:33:37 GMT

the problem is that it is not text, but numeric.

and all missing values, wether normal or special, show in excel as .

in SAS i am fine.

I will post an example tomorrow.

Re: special missing values

RW9 — Thu, 03 Apr 2014 14:38:45 GMT

Perhaps you could convert the column to character prior to exporting to Excel? Will wait to see example.

options missing='$';
data have;
attrib test format=8.;
test=12;
output;
test=.;
output;
run;
data want (drop=test);
set have;
attrib text_for_export format=$10.;
test_for_export=strip(put(test,best.));
run;
proc export...;

Re: special missing values

christa — Fri, 04 Apr 2014 09:44:09 GMT

Here is my example (how can i post it nicely?)

Re: special missing values

RW9 — Fri, 04 Apr 2014 10:48:31 GMT

Hi,

Sorry, not sure what your example is trying to do. I have changed your code slightly to get it working, however the output from the tabulate looks pretty much identical to the input dataset other then three new variables _TYPE_, _PAGE_, _TABLE. Also I note that the proc tabulate itself is not honoring the missing value per the dataset as you have no indicator - only for the datastep. So try using "options missing=B" at the start of your program.

This code works and shows 'B' in the output, it does this by putting the value into a character variable and using that. (Note you can use the out= from the proc tabulate, and then post process that before outputting to Excel).

options missing=B;
data test;
missing B;
input v_id $ v_value;
datalines;
1 1
2 0
3 .B
4 0
5 -1
6 55
8 4003
;
run;

data inter;
set test;
attrib my_val format=$10.;
my_val=strip(put(v_value,best.));
run;

ods _all_ close;
ods tagsets.excelxp file="S:\Temp\Rob\test.xls" style=statistical options (frozen_headers="1" absolute_column_width="none" zoom="80");
ods tagsets.excelxp options (sheet_name="Test");
proc report data=work.inter nowd split="~"
            style(header)=[background = #DBE5F1 foreground = #000099 font_weight = bold ]
            style(column)=[just=l borderrightcolor=white borderleftcolor=white
                           borderleftwidth=1 bordertopcolor=white borderbottomcolor=white
                           borderrightwidth=1 bordertopwidth=1 borderbottomwidth=1];
columns v_id my_val;
define v_id / "Id";
define my_val / "Value";
run;
ods tagsets.excelxp close;

Re: special missing values

christa — Fri, 04 Apr 2014 11:10:33 GMT

Sorry if I did not show exactly where my problem is. I need a numeric field in excel in a specific format. To get this, the xml written by SAS needs to show "real number" in most places, but specific Characters (such as . , X, B) in others.

I hoped to achieve this with special missing values, but cant get it working.

P.S. I just missed this line while copying my code:

missing=B;

Re: special missing values

RW9 — Fri, 04 Apr 2014 12:30:34 GMT

I am afraid I don't know of a direct way of doing that. The specific missing character functionality you are talking about is a SAS specific item, Excel does not have that as missing is just missing. So from before, you would need to convert it character for Excel to show characters. The only other options I can think of is:

- Change your actual missings to a specific number which can't happen, e.g. -999999.

- Post process the XML file which you generated, so read it in as a text file and search for:

Then write your specific character in where the xx is.

- Alternatively use Excel to replace blanks after export.

Re: special missing values

Andre — Fri, 04 Apr 2014 12:41:46 GMT

Lateral answer for your "nicely" question

My trick is to copy the sas colored code from the pgm file to a new temporary word file without too much space gap between lines

then in a second step copy again the colored code from word to here

Re: special missing values

Peter_C — Fri, 04 Apr 2014 14:11:40 GMT

you might have success using traffic lighting to set the background formatting for distinctive missing values

and remembering that the excel custom format allows separate custom formats (separated by semi-colons) for positive negative zero and text values.

However this approach wouldn't distinguish between different special missing values