PROC Export to EXCEL preserves a fake space

[Quentin]

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. 

[terryeastman]

Thank you

[terryeastman]

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.

[Tom]

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?

[terryeastman]

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.

[Quentin]

Still confused, what is SAS / MLE?  Is MLE a SAS product / solution?

[terryeastman]

MLE is an 'internal product' created within TD using SAS Studio as the base.