The SAS Output Delivery System and reporting techniques

Empty fields in Excel

Reply
Occasional Contributor
Posts: 7

Empty fields in Excel

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

SAS Super FREQ
Posts: 8,740

Re: Empty fields in Excel

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

Occasional Contributor
Posts: 7

Re: Empty fields in Excel

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

Occasional Contributor
Posts: 7

Re: Empty fields in Excel

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


Super User
Super User
Posts: 7,392

Re: Empty fields in Excel

I think you may want to look at:

options missing=;

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

Occasional Contributor
Posts: 7

Re: Empty fields in Excel

Relating to:

I think you may want to look at:

options missing=;

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

Bjørn

Super User
Super User
Posts: 7,392

Re: Empty fields in Excel

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

Occasional Contributor
Posts: 7

Re: Empty fields in Excel

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

Super User
Posts: 17,774

Re: Empty fields in Excel

Why do you need this?

Super User
Super User
Posts: 7,392

Re: Empty fields in Excel

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.

Occasional Contributor
Posts: 7

Re: Empty fields in Excel

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

Occasional Contributor
Posts: 7

Re: Empty fields in Excel

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

Super User
Posts: 17,774

Re: Empty fields in Excel

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. 

Ask a Question
Discussion stats
  • 12 replies
  • 2108 views
  • 4 likes
  • 4 in conversation