PROC Export to EXCEL preserves a fake space

[terryeastman]

Using SAS Studio Datapool issue Detail:

Create a SAS data sets with a few variables containing nulls.
Export to excel
download to local drive
explore the nulls with =isblank(T2)
result is FALSE
click on cell T2 and then in the box displaying null value
then click in any other cell. NOTE: did not touch the keyboard. ie did not change the value of cell T2
result changes to TRUE
The result is always TRUE when running the same process not in SAS Studio.
Question: What settings need to change in SAS Studio to make SAS / excel behave when using SAS / Excel outside SAS Studio ?
PC SAS and SAS in DCJ environment pose no issue. The issue exists only in SAS Studio
I did try three reruns of the code with each change
1. COMPRESS fnt
2. STRIP fnt
3. OPTIONS MISSING='';
All outputs had the same result of a 'fake' blank in the cell T2
NOTE: Using notdp

[Tom]

Can you clarify what is the issue exactly?  I assume ISBLANK() is some EXCEL function?  What does it test?  Does it test if the cell is EMPTY or if the only characters it has a spaces?

Is the variable in question NUMERIC or CHARACTER?  If CHARACTER what value does it have in the SAS dataset?  Is it completely filled with spaces? or does it have some other invisible code, like a TAB or a LINEFEED or "non-breaking" space?  If NUMERIC does the variable have a FORMAT attached to it?  And what is the setting for the MISSING= option when you run the "export"?

What exactly do you mean by EXPORT.  Did you run PROC EXPORT?  If so what value did you use for the DBMS= option of the PROC EXPORT statement?  If not then what exactly did you do to export the dataset?

And what the two environments you are using.  What is the version of SAS that you are using? You see the version you can check the value of SYSVLONG automatic macro variable.  Other than SAS/Studio are you using some other interface to run SAS code in the other environment?  Are you running with SAS Display Manager?  Enterprise Guide? Something else?

What is a DCJ environment?

[terryeastman]

PROC EXPORT to excel appears to insert a blank when the SAS value is null.   You can test this by using the excel isblank function.  

Using PC SAS, a null value in the SAS dataset results in a null value in the excel.  Using SAS Studio, it results in a blank in excel.  Our down stream users of our report can not accept blanks ... only nulls

[Tom]

---

@terryeastman wrote:

PROC EXPORT to excel appears to insert a blank when the SAS value is null.   You can test this by using the excel isblank function.  

Using PC SAS, a null value in the SAS dataset results in a null value in the excel.  Using SAS Studio, it results in a blank in excel.  Our down stream users of our report can not accept blanks ... only nulls

---

I will try to translate. 

By "NULL" you mean that the SAS character variable only contains spaces. SAS character variables are fixed length and padded with spaces, so they are never "null".  SAS numeric variables can have 28 different missing values.  The normal missing value, represented by a single period in SAS code could be considered kind of like a "null" value.  The other 27 special missing values, represented by in code by a period followed by a single letter or underscore, might also be considered "null" for some operations.

By "NULL" you mean the cell in EXCEL is completely empty.  And the ISBLANK() function in Excel is not testing if the value in the cell only has blanks, but instead is testing if the cell is empty.  

If you use PROC EXPORT with DBMS=XLSX then character variables that only contain spaces are set to an empty string in EXCEL.  Numeric variables with a missing value are set to an empty cell.  So the ISBLANK() function will return TRUE for the numeric variable and FALSE for the character variable.

Perhaps the reason you are seeing different results on the two different systems is because in one case the SAS variable was NUMERIC and in the other it as CHARACTER.

You can test if the value is empty in EXCEL by comparing it to the string created by typing two double quote characters next to each other.  This will work for values created from both numeric and character variables.

For example to check if the second cell in the second column is empty you could use:

=IF(B2="",TRUE,FALSE)

[terryeastman]

DATA TEST;

  do x=1 to 100;

    y='';

  end;

RUN;

PROC EXPORT DATA=test  DBMS=xls

                                  outfile="o:\SAS_file.xls";

RUN;

The result should show column y as 100 null values in Excel

using isblank excel function, the result is FALSE.  It should be TRUE

A comment from the MLE support team was ...

Cursory examination says its possible that the SAS export or the change of environment (Linux Vs. Windows) means the cell is loading with an empty string instead of true empty until interacted with.
However this is a question regarding SAS export behavior on Linux Vs Windows, and would better be put to SAS support. Trzaskos, Janel J We have a community hub with SAS for such questions, correct?

Thank you

Terry

[Cynthia_sas]

Hi:

  If you run PROC CONTENTS, you should see that the variable Y has been defined as character with a length of 1 byte. SAS represents missing values for a character variable as a space. This is essentially what your assignment statement is telling SAS to assign to the value for Y.

  Just because your assignment statement has

y='' (with the 2 quotes next to each other), in your program it is the same as if you had used

y=' '; (quote space quote). Either way the stored value for Y is a missing value -- or space for the character variable. 

  That should create Y as a character value with a length of 1. So when the data is exported to Excel, the space character is what fills the cell. You call it a "false space" but in fact, it is a real space and your code assigned the space to Y.

Cynthia

[terryeastman]

The actual data step has been in use for nearly 2 decades. The issue of the stored results in excel only started as we move to SAS Studio / MLE

[Tom]

Why are you making an XLS file instead of the standard XLSX file format?  Note that creating an XLS file instead of an XLSX file does not change how EXCEL handles the blank cells generated from character values that only contain spaces.

Why did you use this statement in your SAS code?

y='';

That is poor coding style as it will confuse novice SAS programmers that are trying to understand what it is doing.

Since you did not tell the data step what type of variable Y should be, nor what storage length it should have, the data step compiler will GUESS that it should be character with a length of  one byte.  Since SAS character values are fixed length and padded with spaces it will be much clearer to show that space in the SAS code.

y=' ';

What/who is the "MLE support team'?

[terryeastman]

This was a simple example of the true data step. 

[Cynthia_sas]

Hi:

  If this is a new behavior when you change operating systems/editors to create the SAS data, then this is probably an issue you want to raise with SAS Tech Support. They would need to look at all your code and understand the previous operating system/editor versus the current operating system/editor to explain why the difference occurs. You can always open a case with SAS Tech Support by sending mail to support@sas.com .

Cynthia

[terryeastman]

Thank you.  I started that too.

[Ksharp]

Yes. There is a white blank in T2 column. So you would got FALSE .
If you need TRUE, try :
=IF(LEN(TRIM(T2))=0, TRUE, FALSE)

[terryeastman]

The issue stems from the downstream user of our output no longer able to use it (after 2 decades of non-issue) b/c SAS Studio / MLE either populates excel differently or, as an MLE support person suggested, there may be an issue when transferring the excel output out of MLE / SAS Studio 

[Ksharp]

I think it has nothing to do with sas.
In sas, a blank means missing value of character variable, unlike Excel or other DataBase which could have a special character NULL/NA to represent the missing value . So you can't apply isBlank() or isNull() into sas data.

[Patrick]

@terryeastman 

I've used Proc Export to create a .xlsx (not .xls). 

I can replicate what you describe and for me it happens both in a Linux and Windows environment using SAS EG.

I don't believe that it has anything to do with "PC SAS" or SAS Studio but with the version of SAS and potentially Microsoft Excel components (if SAS uses them at all for creation of the xlsx) where you execute your code. 

If I look into the cell value itself then it's NULL (not a single blank) but function isnull(<cell>) returns FALSE. Once I click into the cell and then into the pane where I would type formulas and then into another cell the value changes to TRUE.

In my test case if I compare the xml of the sheet before and after these actions then I can see that it changed - the highlighted bit got removed. No clue what this means.

Using Shift + F9 to recalculate all formulas in the active sheet had no impact. The result of isnull(<cell>) remains FALSE even though there is not even a blank in the cell.

What did return the expected result what using formula LEN(<cell>)=0

I suggest you take this up with SAS Tech Support. Don't expect a speedy resolution though as it's likely something in the bowels of SAS or Microsoft.

In the meantime the workaround using LEN(<cell>)=0 instead of isnull(<cell>) should get you going.

From what I understand the "fake space" is not an actual blank but the observed issue is due to the generated xml that makes the isnull() function return the wrong result.