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)
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.
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.
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
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?
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.
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.
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.
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.
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.
65k rows limit
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.
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.
Select SAS Training centers are offering in-person courses. View upcoming courses for: