BookmarkSubscribeRSS Feed
VojtechHerrmann
Calcite | Level 5

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)

12 REPLIES 12
ballardw
Super User

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.

VojtechHerrmann
Calcite | Level 5

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.

VojtechHerrmann
Calcite | Level 5

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.

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

VojtechHerrmann
Calcite | Level 5

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.

RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

VojtechHerrmann
Calcite | Level 5
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.
RW9
Diamond | Level 26 RW9
Diamond | Level 26

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.

VojtechHerrmann
Calcite | Level 5

65k rows limit

Kurt_Bremser
Super User

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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 12 replies
  • 3048 views
  • 0 likes
  • 4 in conversation