DATA Step, Macro, Functions and more

How to make cells empty for Excel ISBLANK function?

Reply
Occasional Contributor
Posts: 8

How to make cells empty for Excel ISBLANK function?

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)

Super User
Posts: 11,343

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

Posted in reply to VojtechHerrmann

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.

Occasional Contributor
Posts: 8

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

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.

Super User
Posts: 7,832

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

Posted in reply to VojtechHerrmann

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

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Super User
Posts: 7,832

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

Posted in reply to VojtechHerrmann

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?

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Occasional Contributor
Posts: 8

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

Posted in reply to KurtBremser

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.

Super User
Super User
Posts: 7,977

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

Posted in reply to VojtechHerrmann

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.

Occasional Contributor
Posts: 8

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

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.

Super User
Super User
Posts: 7,977

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

Posted in reply to VojtechHerrmann

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.

Occasional Contributor
Posts: 8

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

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.
Super User
Super User
Posts: 7,977

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

Posted in reply to VojtechHerrmann

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.

Occasional Contributor
Posts: 8

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

65k rows limit

Super User
Posts: 7,832

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

Posted in reply to VojtechHerrmann

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.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Ask a Question
Discussion stats
  • 12 replies
  • 594 views
  • 0 likes
  • 4 in conversation