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
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
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
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
I think you may want to look at:
options missing=;
Relating to:
I think you may want to look at:
options missing=;
AFAIK you cannot specify "Empty" as the missing value.
Bjørn
You should be able to do: options missing='';
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
Why do you need this?
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.
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
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
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.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.