BookmarkSubscribeRSS Feed
Janitor
Calcite | Level 5

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

12 REPLIES 12
Cynthia_sas
SAS Super FREQ

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

Janitor
Calcite | Level 5

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

Janitor
Calcite | Level 5

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


RW9
Diamond | Level 26 RW9
Diamond | Level 26

I think you may want to look at:

options missing=;

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

Janitor
Calcite | Level 5

Relating to:

I think you may want to look at:

options missing=;

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

Bjørn

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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

Janitor
Calcite | Level 5

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

Reeza
Super User

Why do you need this?

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

Janitor
Calcite | Level 5

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

Janitor
Calcite | Level 5

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

Reeza
Super User

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. 

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 12 replies
  • 6472 views
  • 4 likes
  • 4 in conversation