topic Re: Empty fields in Excel in ODS and Base Reporting

Empty fields in Excel

Janitor — Mon, 05 May 2014 13:52:47 GMT

Hi,

I have a dataset - some of the fields are empty. I would like to output this dataset to Excel, but I need the empty fields to "really" be empty (i.e. not just a space character).

Any ideas?

Bjørn

Re: Empty fields in Excel

Cynthia_sas — Mon, 05 May 2014 15:44:02 GMT

Hi:

How are you creating your output for Excel - -using PROC EXPORT, the LIBNAME engine or ODS? And, if you're using ODS, which method are you using ODS CSV, HTML-based methods or XML-based methods? Remember that missing values in SAS are represented by a . (missing numeric) or a space (missing character). So I don't quite understand what you mean by "really" empty -- SAS data that has missing values will show the SAS missing values when exported to Excel.

In addition how you create output can be impacted by the version of SAS that you have, the version of Microsoft that you have and the "bitness" 32 bit or 64 bit of both. Have you searched for Tech Support notes on compatibility for export and what you need to do. That is something that has to be ironed out before you worry about missing values.

cynthia

Re: Empty fields in Excel

Janitor — Mon, 05 May 2014 16:15:52 GMT

I can (more or less) utilize any method for creating the Excel file I want to, as long as I end up with the excel file (XLS, XML or whatever format) I need 😉

ODS would certainly be preferable, but is not a requirement.

Bjørn

Re: Empty fields in Excel

Janitor — Tue, 06 May 2014 13:33:58 GMT

Rereading the point:

Remember that missing values in SAS are represented by a . (missing numeric) or a space (missing character). So I don't quite understand what you mean by "really" empty -- SAS data that has missing values will show the SAS missing values when exported to Excel.

Let me try to rephrase my question:

I would like to use a missing value of "empty" (not a space character or other value, but "nothing") when exporting data to Excel.

Bjørn

Re: Empty fields in Excel

RW9 — Tue, 06 May 2014 13:41:35 GMT

I think you may want to look at:

options missing=;

SAS(R) 9.2 Language Reference: Dictionary, Fourth Edition

Re: Empty fields in Excel

Janitor — Tue, 06 May 2014 14:07:45 GMT

Relating to:

I think you may want to look at:

options missing=;

AFAIK you cannot specify "Empty" as the missing value.

Bjørn

Re: Empty fields in Excel

RW9 — Tue, 06 May 2014 14:11:52 GMT

You should be able to do: options missing='';

Re: Empty fields in Excel

Janitor — Tue, 06 May 2014 14:17:16 GMT

You should be able to do: options missing='';

Using this with proc print yields a space character in excel cells that are supposed to be empty - I'm looking for a solution that leaves the fields "empty" (i.e. does not contain any data at all).

Bjørn

Re: Empty fields in Excel

Reeza — Tue, 06 May 2014 14:19:15 GMT

Why do you need this?

Re: Empty fields in Excel

RW9 — Tue, 06 May 2014 14:21:22 GMT

Agree with Reeza. Just to add, if its giving a blank then just add a datastep before your print:

data new;

set old;

attrib new_var format=$200.;

if old_var=. then new_var='';

else new_var=put(old_var,best.);

run;

So you end up with a char field to print.

Re: Empty fields in Excel

Janitor — Tue, 06 May 2014 14:23:34 GMT

Why do you need this?

The data is to be used in further processing and to alleviate any user work (and possibilities for errors) a simple copy / paste is to be used - the receiving system cannot accept "space" as missing values, it requires "empty" when data is missing.

Bjørn

Re: Empty fields in Excel

Reeza — Tue, 06 May 2014 14:25:59 GMT

I've seen a work around for this on the excel side - a macro that converts every space to a blank or DDE process that does the same. I haven't tested any other methods though.

If you're only copying and pasting in Excel there may be other options, ie DDE.

Re: Empty fields in Excel

Janitor — Tue, 06 May 2014 15:15:14 GMT

I realized that PROC REPORT does indeed create "empty" fields when text variables are empty, so working off this idea:

data new;

set old;

attrib new_var format=$200.;

if old_var=. then new_var='';

else new_var=put(old_var,best.);

run;

And using PROC REPORT with:

define new_var / 'Var' right display style={tagattr='format:#,##0.00'};

gets the job done - the excel cells contains real numbers the numeric field contains a value (formatted as a number) and the cells are empty if the numeric field is missing.

Bjørn