topic Re: Export to excel in SAS Programming

Export to excel

Anuz — Thu, 28 Oct 2021 23:40:05 GMT

Hi All,

I am after some suggestions.

I have a huge dataset around a million records.

I know and have done a proc export to csv and it works fine and is quick too.

I tried the ods excel option but it failed - ERROR: The SAS System stopped processing this step because of insufficient memory. On further reading up, I understand that the ods excel options holds the whole table in memory before creating the excel and hence the failure. I don't have the option to change the MEMSIZE at the moment.

Even though the csv file is ok, I need to be able to add some formatting and autofilter etc.

I see that ods tagsets.excelxp could be an option as it holds only cell at a time in memory if I am not mistaken.

But is that the only option I have or has anybody else got a better suggestion please ?

Thank you in advance.

Re: Export to excel

Reeza — Fri, 29 Oct 2021 03:01:41 GMT

What version of SAS do you have? Prior to SAS 9.4M3 ODS EXCEL was pre-production and buggy. 9.4M5 is fairly stable.

And how 'around a million records' are you? Excel has a row limit of 1,048,576 rows.

@Anuz wrote:

Hi All,

I am after some suggestions.

I have a huge dataset around a million records.

I know and have done a proc export to csv and it works fine and is quick too.

I tried the ods excel option but it failed - ERROR: The SAS System stopped processing this step because of insufficient memory. On further reading up, I understand that the ods excel options holds the whole table in memory before creating the excel and hence the failure. I don't have the option to change the MEMSIZE at the moment.

Even though the csv file is ok, I need to be able to add some formatting and autofilter etc.

I see that ods tagsets.excelxp could be an option as it holds only cell at a time in memory if I am not mistaken.

But is that the only option I have or has anybody else got a better suggestion please ?

Thank you in advance.

Re: Export to excel

SASKiwi — Fri, 29 Oct 2021 04:25:34 GMT

In my experience ODS is to be avoided with large amounts of data as it is VERY resource intensive. Either you will run out of memory or it will take a huge amount of time to run.

Why not do your formatting in Excel after reading in the CSV? BTW managing a million rows in a spreadsheet is pushing the boundaries a bit...

Re: Export to excel

whymath — Fri, 29 Oct 2021 05:26:56 GMT

ODS is too slow for very large datasets. How about use proc export to generate a .xlsx file and then use DDE skill to format it?

Re: Export to excel

Anuz — Fri, 29 Oct 2021 08:34:35 GMT

@Reeza

9.4 M3

Very close to the million - but wont hit the limit - 1,048,576

Re: Export to excel

Anuz — Fri, 29 Oct 2021 08:52:39 GMT

@whymath ok. I haven't really used DDE to format.

My plan is to get my SAS script to be run on a scheduler. This creates a xls report on a daily basis. I need to some basic formatting like autofilter,title and a background color to the column headings.

Would that be possible ? Can you give me some pointers maybe ?

Thank you.

Re: Export to excel

ballardw — Fri, 29 Oct 2021 09:09:35 GMT

@whymath wrote:

ODS is too slow for very large datasets. How about use proc export to generate a .xlsx file and then use DDE skill to format it?

One reason not to use DDE is that there are programs out there that use the same communications stuff that DDE uses and DDE may not work at all. Cisco Jabber is one such. You have to KILL the processes involved with Jabber not just close the program window if you have it running before DDE will work.(Ask me just how long it took to determine that was the cause the one job run once a year that I used DDE with and failed with nothing happening.) Since Microsoft has basically stopped pushing/supporting DDE it becomes much harder to use because compliance with DDE for other applications is no longer a requirement and who know how many other programs are interfering with the communications that DDE uses.

Re: Export to excel

ballardw — Fri, 29 Oct 2021 09:17:04 GMT

Once upon a time about 80 lines was typical for a "page" of text. A file with around a million lines then consists of around 12,500 pages using that definition.

Who is reading that?

What kind of formatting is so important in a 12000 page document?

Autofilter??? Might spend a LOT time waiting for that feature to return much of anything. Excel spends an awful lot of overhead with its internal displays. If a column contains thousands of value levels, which seems entirely likely with a million records.

You might be better off making separate sheets for the levels of the most common "filtered" variables.

Re: Export to excel

Kurt_Bremser — Fri, 29 Oct 2021 09:48:10 GMT

@Anuz wrote:

@Reeza

9.4 M3

Very close to the million - but wont hit the limit - 1,048,576

Which is just a matter of time and will render all the time you spent wasted. Data keeps growing, always (40+ years in IT speaking here). Even when you don't expect it.

A million of rows has no place in a toy tool like Excel. In our company, use of Excel for BI means is explicitly forbidden because we already had money-costing issues by people doing that.

Do all the heavy lifting in SAS, and write nice final reports (which won't extend beyond a few screen pages) to Excel so you pamper your managers.

Even better, supply the final reports as HTML reports on the server.

Re: Export to excel

Anuz — Fri, 29 Oct 2021 12:52:51 GMT

@Kurt_Bremser understand.

The scenario is that I need to make this data available to a BAU team that don't have access to databases or tools (like SAS) and are non-technical.

When they need they need to be able to access this data for analysis.

Is there any other way you can suggest that I can provide this data to the team ? I am now thinking maybe make it available as a table that can maybe open in MS access. at least that way they can query that table with filters in it.

Is there a way I can export the table to an MS access friendly extract from SAS ? Any thoughts.

Re: Export to excel

Reeza — Fri, 29 Oct 2021 16:01:17 GMT

Create a CSV or data source and link your Excel file to it. Update the CSV file and set the Excel file to refresh connections when opened.
https://www.intheblack.com/articles/2016/06/01/an-easier-way-to-use-csv-files-in-excel

Re: Export to excel

Reeza — Fri, 29 Oct 2021 16:10:00 GMT

Create a template Excel file.
Create your filters/formats in a nice Excel TABLE - must be an Excel Table structure.
Link that table to a different source, CSV.
Use PROC EXPORT to export to the CSV/Sheet and then have your pivot table updated.

Re: Export to excel

Kurt_Bremser — Fri, 29 Oct 2021 19:56:39 GMT

What you export here is data, not a report, so no formatting is needed. Use data steps to write CSV files.

Re: Export to excel

Kurt_Bremser — Sat, 30 Oct 2021 07:52:14 GMT

PS any tabular system worth the money can read csv files.

Re: Export to excel

Bravez — Thu, 31 Mar 2022 19:52:45 GMT

Our ongoing hackathon project can inject values into Excel/ Word/ PowerPoint cells without Microsoft DDE, it might be a solution.