topic Re: How to make cells empty for Excel ISBLANK function? in SAS Programming

How to make cells empty for Excel ISBLANK function?

VojtechHerrmann — Thu, 10 Nov 2016 16:48:27 GMT

Hi,

whatever I do, I can not make blank cells be really "empty", after I export the dataset to xlsx.
Excel function ISBLANK returns always FALSE, no matter what data type the column is (datetime, number,string). Although, Cell.Value = "" (in Excel).

I created an xlsx file which had really blank cells (ISBLANK returned TRUE), I imported it into SAS, without any change exported it back as xlsx and still i got FALSE from ISBLANK function.

I tried to convert column to string, rewrite on "" (when the cell was null) and then convert it back to its original data type, did not help.

Any advice?

Thanks, Vojtech
SAS vers. 7.1 HF1 (7.100.0.2002) (32-bit)

Re: How to make cells empty for Excel ISBLANK function?

ballardw — Thu, 10 Nov 2016 18:39:32 GMT

I assume that you are exporting the data to XLSX or XLS using proc export. Try sending the data set to CSV and then opening in Excel and see if the behavior is the same.

Note that SAS has "special" behaviors for missing. I am not sure what Excel receives as value in XLSX but I doubt is what Excel uses for "blank" to respond to the ISBLANK function.

Of course I generally do all the data manipulation is SAS and very seldom every use Excel on SAS output.

Re: How to make cells empty for Excel ISBLANK function?

Kurt_Bremser — Thu, 10 Nov 2016 21:27:03 GMT

Do the logic in SAS and use Excel solely for display. Why use a kid's applecart when you have a Mack with the top speed of a Ferrari?

Re: How to make cells empty for Excel ISBLANK function?

VojtechHerrmann — Fri, 11 Nov 2016 08:31:56 GMT

Xlsx file with blank cells is appoximately 50% smaller then with "blank" cells on my dataset. When speaking about hundreds of GB, it makes difference.

Re: How to make cells empty for Excel ISBLANK function?

VojtechHerrmann — Fri, 11 Nov 2016 08:35:21 GMT

It's the same, when I export the dataset as csv.
I do not mind that the ISBLANK function returns TRUE, but when there are really blank cells, excel file is much smaller, I just want to save storage space and I observed, that this "fake blank" cells make the file much larger.

Re: How to make cells empty for Excel ISBLANK function?

Kurt_Bremser — Fri, 11 Nov 2016 08:47:51 GMT

So your issiue is not so much a logical one, but a storage one.

I've found a thread that might be helpful (haven't tried it myself):

https://communities.sas.com/t5/ODS-and-Base-Reporting/Empty-fields-in-Excel/td-p/144707

Re: How to make cells empty for Excel ISBLANK function?

RW9 — Fri, 11 Nov 2016 09:14:21 GMT

Why are you using Excel for data as big as "hundreds of GB"? Do you believe someone will read a file which contains millions of records or observations? From my expeirence most people wont even scroll the window lett alone paging.

What is the puropse of the file, are you sending data to someone? If so then consider a proper transfer format, CSV, XML, Json etc. and forget the whole "excel" part.

If you producing a report, then consider summarising the data, or graphing the data.

Re: How to make cells empty for Excel ISBLANK function?

VojtechHerrmann — Fri, 11 Nov 2016 09:25:13 GMT

It's not just one file, one file holds up to 10 GB of data. These files are stored as raw data sources for large excel files and automatic VBA projects, which use them to provide required outputs... No users open these exact files.

Re: How to make cells empty for Excel ISBLANK function?

RW9 — Fri, 11 Nov 2016 09:47:32 GMT

Sounds like CSV would be the ideal choice then - small, can be opened with Excel quite simply.

Although to be honest, it sounds like your trying to use Excel as a database, good luck with that.

Re: How to make cells empty for Excel ISBLANK function?

VojtechHerrmann — Fri, 11 Nov 2016 09:52:19 GMT

I export it as xlsx and then convert it to xlsb, which is even smaller then csv. However the blank cells would decrease the size even more.

Re: How to make cells empty for Excel ISBLANK function?

Kurt_Bremser — Fri, 11 Nov 2016 09:58:48 GMT

So your BI logic is fractured across platforms. VERY BAD IDEA.

Take VBA and Excel out of the picture, and consolidate everything into SAS.

Excel has become an absolute no-no in our organization for business logic, and this is enforced by our auditors. Too much **** happened.

Re: How to make cells empty for Excel ISBLANK function?

RW9 — Fri, 11 Nov 2016 10:03:12 GMT

Just to add, and not going any further as this is a SAS forum not an office one. XLSB means proprietary binary file format, which is why it is smaller - but obviously you lose portability, and openess. If you are happy with that then why not just directyl export the data via proc export to XLS which is virtually the same binary proprietary format and save the conversion process.

Re: How to make cells empty for Excel ISBLANK function?

VojtechHerrmann — Fri, 11 Nov 2016 10:05:23 GMT

65k rows limit