topic Re: PROC Export to EXCEL preserves a fake space in SAS Programming

PROC Export to EXCEL preserves a fake space

terryeastman — Tue, 17 Jun 2025 17:31:29 GMT

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

Re: PROC Export to EXCEL preserves a fake space

Tom — Tue, 17 Jun 2025 18:13:07 GMT

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?

Re: PROC Export to EXCEL preserves a fake space

terryeastman — Tue, 17 Jun 2025 18:18:58 GMT

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

Re: PROC Export to EXCEL preserves a fake space

Tom — Tue, 17 Jun 2025 18:58:40 GMT

@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)

Re: PROC Export to EXCEL preserves a fake space

terryeastman — Tue, 17 Jun 2025 19:59:18 GMT

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

Re: PROC Export to EXCEL preserves a fake space

Cynthia_sas — Tue, 17 Jun 2025 21:48:50 GMT

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

Re: PROC Export to EXCEL preserves a fake space

Tom — Wed, 18 Jun 2025 03:00:53 GMT

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'?

Re: PROC Export to EXCEL preserves a fake space

Ksharp — Wed, 18 Jun 2025 03:48:54 GMT

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)

Re: PROC Export to EXCEL preserves a fake space

terryeastman — Wed, 18 Jun 2025 12:43:10 GMT

This was a simple example of the true data step.

Re: PROC Export to EXCEL preserves a fake space

terryeastman — Wed, 18 Jun 2025 12:44:51 GMT

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

Re: PROC Export to EXCEL preserves a fake space

terryeastman — Wed, 18 Jun 2025 12:47:58 GMT

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

Re: PROC Export to EXCEL preserves a fake space

Cynthia_sas — Wed, 18 Jun 2025 14:21:55 GMT

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

Re: PROC Export to EXCEL preserves a fake space

terryeastman — Wed, 18 Jun 2025 14:25:32 GMT

Thank you. I started that too.

Re: PROC Export to EXCEL preserves a fake space

Ksharp — Thu, 19 Jun 2025 00:54:55 GMT

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.

Re: PROC Export to EXCEL preserves a fake space

Patrick — Thu, 19 Jun 2025 11:45:41 GMT

@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.

Re: PROC Export to EXCEL preserves a fake space

Quentin — Thu, 19 Jun 2025 12:35:19 GMT

Interesting, I googled a bit of that XML block, and google's AI came back with below.

In the context of the XML structure of an Excel file (which is in the Open XML format, essentially a zipped collection of XML files), the code snippet <c r="A3" t="s"> represents a cell within a worksheet.

Here's a breakdown:

<c>: This is the XML tag representing a cell.
r="A3": This attribute specifies the cell's location or reference, which is "A3" in this case.
t="s": This attribute indicates the data type of the cell's value. The value "s" signifies that the cell contains a shared string.

Shared String Table:

When the t attribute is set to "s", the actual text content of the cell is not stored directly within the <c> element. Instead, the <v> element inside the <c> element will contain an index that refers to an entry in the Shared String Table.

The Shared String Table is a separate part of the Excel XML file, typically found in a file named xl/sharedStrings.xml. It's a space-saving optimization where duplicate strings are stored only once and referenced by multiple cells. This means that multiple cells containing the same text can all point to the same entry in the Shared String Table, reducing file size.

In summary, <c r="A3" t="s"> tells us:

This is a cell.
Its location is A3.
It contains a string, and its value is stored in the Shared String Table, referenced by an index within the <v> element.

Re: PROC Export to EXCEL preserves a fake space

terryeastman — Thu, 19 Jun 2025 13:05:42 GMT

Thank you

Re: PROC Export to EXCEL preserves a fake space

terryeastman — Mon, 23 Jun 2025 12:59:57 GMT

The core issue appears to be populating Excel with a LIBNAME statement vs PROC EXPORT.

LIBNAME treats a NULL as a NULL. PROC EXPORT introduces a fake blank.

Using a LIBNAME in MLE allows for only an XLSX extension (I'm waiting for MLE support to see is XLS is in any way available besides PROC EXPORT where it fails the test). Our downstream users insist on an XLS report.

In a perfect world, in SAS Studio MLE, we would use

LIBNAME rbretail pcfiles path=''&transformations_macro_var";

In our non SAS Studio code we have need using the following for over 15 yrs LIBNAME rbretail pcfiles path="&PATHOUT\rb_retail_B3R.xls";

They will not accept xlsx.

Re: PROC Export to EXCEL preserves a fake space

Tom — Mon, 23 Jun 2025 13:44:36 GMT

That is not the situation you asking about in your original post. So now your question makes more sense.

You have not actually been using PROC EXPORT before and then had it change its behavior.

Instead you have been using a SAS PCFILES server to use EXCEL to create the XLS file.

To see what has changed you need to check what servers you are using to run the PCFILES server process and what version of EXCEL is running on those server. Has something changed there?

Is there some reason you cannot use the PCFILES libref engine with your MLE sessions? Does it not allow you to define your own librefs?

Re: PROC Export to EXCEL preserves a fake space

terryeastman — Tue, 24 Jun 2025 13:59:29 GMT

You're right. That wasn't the situation of my original post.

It appears SAS procedures do not work the same as a LIBNAME statement when interacting with EXCEL.

BOTH PROC EXPORT and PROC DBLOAD generate fake blanks regardless of the server environment - PC SAS, UNIX, SAS Studio. LIBNAME preserves a true null w/o turning it into a fake blank. I tested and confirmed this.

Regretfully, we can not use a LIBNAME statement in SAS / MLE b/c the enduser insists on an XLS extension. MLE was designed such that PCFILES is not an option. Only XLSX extensions are possible with a LIBNAME statement in MLE. Confirmed with MLE support.