topic Re: SAS Add-In in Excel: Why Is Data Loading Taking So Long? in SAS Enterprise Guide

SAS Add-In in Excel: Why Is Data Loading Taking So Long?

laika_matias — Thu, 28 Sep 2023 20:03:48 GMT

I'm facing an issue where loading data from SAS or applying filters takes a considerable amount of time. However, once the data is loaded, I have no issues quickly analyzing or modifying it, such as creating pivot tables. What could be the reason for this extended data loading time? Could the problem be related to something other than insufficient RAM?

Re: SAS Add-In in Excel: Why Is Data Loading Taking So Long?

SASKiwi — Thu, 28 Sep 2023 20:55:41 GMT

Data loading time has nothing to do with RAM. The biggest influences are the network performance between your SAS server and your PC and the size of the data you are downloading. I'm assuming here that you are connecting to a remote SAS server. Are even small tables taking a long time? Has it always been slow or is this something that has just started happening?

Re: SAS Add-In in Excel: Why Is Data Loading Taking So Long?

laika_matias — Fri, 29 Sep 2023 07:52:46 GMT

I initially believed the issue was due to RAM, as I noticed a significant increase in memory consumption while importing data. I attempted to load a dataset with nearly a 1mln records (200MB). However, I'm relatively new to using this tool, and I've observed that server response times are gradually increasing. It also takes several minutes to select filter options, even without setting any filters.

Re: SAS Add-In in Excel: Why Is Data Loading Taking So Long?

laika_matias — Fri, 29 Sep 2023 10:15:41 GMT

Now I tried to load 84MB of data, and it took 7min.

Furthermore, every time I want to import data, I have to open a new Excel. After disconnecting and reconnecting in Excel, SAS components cannot be accessed (SAS-related icons like 'Modify' don't work).

I'm working on a Linux system; could that be the cause?

Re: SAS Add-In in Excel: Why Is Data Loading Taking So Long?

AhmedAl_Attar — Fri, 29 Sep 2023 12:03:17 GMT

Hi @laika_matias

Not sure what other SAS tools you have access to, but typically Excel is not the designated tool for updating SAS data sets!

I would highly recommend talking to your colleagues or your SAS Administrator about the alternatives tools available at your organization.

SAS Add-in for Excel was primarily developed to provide MS Office suite Interface to underlying SAS software capabilities.

Editing a SAS data set is best done using SAS native tools, beside the ability to edit a SAS table/data set could be controlled by one or two levels of enforced security.

Operating System Folder/File permission settings (default)
If you have a Metedata Server to connect to, then potentially there could SAS Metadata Server enforced permissions

The above should explain why the 'Edit' button may be grayed out.

Re: SAS Add-In in Excel: Why Is Data Loading Taking So Long?

SASKiwi — Fri, 29 Sep 2023 22:02:09 GMT

@laika_matias - For those volumes of data you should be using other SAS tools as suggested by @AhmedAl_Attar . Excel simply doesn't scale well as all data has to be loaded into memory. I'm not sure what your disconnecting problem is. I've never had that issue myself. It would be worthwhile opening a Tech Support track for this.