Export to excel on a local PC -One more question

[SP_RANDE]

Hi,
I have a stored process which is invoked from MS Excel to have the data results to be displayed directly in excel. But there is a problem in getting the results to excel when there is data more than about 20k rows. It takes forever to open the data in excel. I have increased the data size limit for sas add-in results option but still does not help it. SAS Add-in alert box says stored process opening results and never opens in excel. Meanwhile system hangs up and need to restart.

Are there any other add in options to take care of this problem?

Thank you!
Raja

[deleted_user]

Excel has been chronically unpredictable or unstable for me when trying to handle data of any reasonable size. It had a limit of 16k rows last century and now allows 64k rows, but at the expense of performance.


I really think you need to look at Excel as the culprit for slow response, not SAS.


The consequence is that it is likely that nothing you do with SAS configuration is going to make a difference.


To prove this, run some tests with data steadily increasing the record counts by 1000 at a time, and note the time it takes to open the data. I would expect the time would increase in a non linear fashion.

Kind regards

David

[SP_RANDE]

David,
Thank you for your reply and clarifications.
Is it true that sas add-in allows only 64k rows limit to display ? Anyway I have never got that many rows of data populated from addin as I have been running into many problems. But I was thinking Add-in Excel take care of the data >64K by adding multiple tabs . This is the main advantage, right? Is it possible only when you open a datasource from sas datasets?

thanks!
Raja

[deleted_user]

"Is it true"... no, it isn't. SAS handles tables many orders of magnitude larger than anything that Excel can handle.

The 64k - 1 row limitation is an Excel limitation, not a SAS one.


In the past I have written code to slice and dice tables so I could populate Excel within its puny limitations. The Add-In may have the functionality to split the output, but since I don't have experience of it, I can't advise further.

Kind regards

David

[Cynthia_sas]

Hi...
When you have the SAS Add-in for Microsoft Office, SAS lives with Excel's limit. There is an option you can set (in the Add-in) that tells SAS how many records to load into Excel at a time.

In class, we show setting the limit to 1500, and then we scroll through an almost million obs table 1500 rows at a time.

Supposedly, Excel is changing the limit for Excel 2007/Vista -- but I have not seen how that will work with SAS 9.2 version of the Add-in.

David is correct that SAS can handle tables of just about any magnitude, depending on the limitations of your particular system configuration. For Excel 2003 the limit on number of rows is 64K. When you use the Add-in, Excel is acting as a VIEWER and the SAS Add-in is fetching observations based on the VIEWER limitations and options you set.

cynthia

[SP_RANDE]

Cynthia,
Thank you for your response. It is true that SAS add-in can load a sas dataset into Excel with a number of rows at a time with an option set in addin .

I have a stored process with a proc print statement and expect the results dataset to be displayed directly into excel. As we are not sure howmany records will be in the output data results, How can I control the output in excel to be displayed(1500 rows at a time as in your example)? I was wondering the option 'Number of records to display' works for the same purpose or not?
I tried but did not work for me. Is there any other option or how to control the data which was generated from stored process used either from proc print or a different procedure?

[Cynthia_sas]

Hi!
Long post warning. Let's talk for a bit about DATA and RESULTS.

When you use the SAS Add-in for Microsoft Office, in Excel, you can OPEN data into an Excel worksheet. SAS Data files are your data stored in a proprietary format that is a rectangular structure of rows and columns. This rectangular structure is stored, internally, in a proprietary format. Here are some of the characteristics of a SAS data set:
-- A SAS data set contains data
-- a SAS data set MIGHT have a description, but does NOT have a TITLE
-- SAS data set variables can be either character or numeric.
-- SAS stores information about how each column of information should be formatted -- but within fairly simple limits. For example, the SALES column can be formatted with the DOLLAR14. format; the JOBCODE column could be formatted with a user-defined format called $JCODE., etc. SAS formats do NOT include anything that has to do with colors and fonts.
-- A SAS data set does NOT have page breaks
-- A SAS data set can only be viewed with SAS or with some product, like the SAS Add-in for Office or the SAS System Viewer or with EG, that "knows" how to open and display a SAS data structure in a readable format.

When you analyze or describe or otherwise perform tasks on SAS data sets, you are using some "procedures" behind the scenes that produce RESULTS. Here are some of the characteristics of RESULTS:
-- RESULTS have titles
-- RESULTS have footnotes
-- depending on the output type or destination, RESULTS may have page breaks (for example, PDF has page breaks, but HTML does not have page breaks)
-- RESULTS can have color and font specifications (For example, column headers can be bold and be placed on a purple background, but the data cells can be placed on an orange background -- garish as that may be).
-- RESULT files generally have a specific kind of viewer or rendering software that you use ...for example, PDF result files are best viewed with Adobe Reader; RTF result files are best viewed with Microsoft Word or a word processor; HTML files are best viewed with a browser; SASReport XML result files are best viewed with SAS Web Report Studio.

Sometimes, when you perform your analysis or task or run a SAS procedure, you can EITHER get a SAS dataset as output from your analysis OR you can get a RESULT file (a report) from your analysis. When you are running stored processes, USUALLY, you are producing RESULTS and not DATA SETS.

So, why did I go on and on about this? Because when you say: "I have a stored process with a proc print statement and expect the results dataset to be displayed directly into excel." That single statement highlights the salient point that will dictate the answer to your question.

When you use PROC PRINT, you are NOT creating a "results dataset" -- you are creating a RESULT REPORT, probably in SASReport or HTML format (depending on your SAS Options for Excel). The fundamental difference between datasets and results is the reason for your inability for display 1500 rows at a time. A SAS data set is defined in the metadata -- it has a location where it lives on a disk device someplace. Therefore, Excel can ask SAS to "serve up" 1500 or 2000 rows of the data at a time. On the other hand, a RESULT report from PROC PRINT in a stored process is not data -- it does not live on a disk device in a form that can be "served up" in chunks. Generally, stored process results come back as STREAMING output -- via the connection between your client application and SAS on the server. One of the characteristics of STREAMING output is that it comes back in one stream.

When you try to open a big DATA SET into an Excel worksheet, SAS serves up the DATA records or rows in chunks, based on your options. When you try to run a stored process that will return a big RESULTS file or report, Excel will give you a message that says:
[pre]
SAS has generated a results file (...name of temp file here...) that is 11371 KB in size, which is larger than the limit specified on the Results tab of SAS Add-in Options (1000 KB). This file could take several minutes to open.
[/pre]
(all the numbers in the above message are based on my test case and my options)

Notice, that nowhere on this message is there a reference to a SAS data set. When you open SAS --> Options in Excel, the number of RECORDS to be served up is on the DATA tab. The default is 500. The way to change how RESULTS get returned is on the RESULTS tab .. .you can the FORMAT (SASReport, HTML or CSV) of the report results, the STYLE of the report results and whether you want to be prompted if results file exceeds a certain size -- the default value is 1000 KB. I never change this default value because I have found that I do not generally have the patience to look at very many report rows at a time.

OK, so back to your question. PROC PRINT is just "printing" or creating a report for a SAS dataset. The value that PROC PRINT is adding is the ability to have titles and footnotes and summary lines. All very nice. But, if your stored process is creating a dataset and then doing a PROC PRINT on that dataset, perhaps you would be better off, creating the dataset in a permanent library and then using Excel to access that data set as a data set (and thus, go through the data set rows or records 1500 rows at a time).

At this point, I have to comment that perhaps your stored process might be written differently -- returning HUGE result/report files is generally not something that stored process users are willing to wait for. If you want the data points in Excel, then have your stored process create a data set that the users can then access. This might require a bit of setup work to make sure that the data set is registered in the metadata, but the setup is well worth the ability to serve up the data in chunks.

There are ways that you could write your stored process to test the results of your query and do some conditional processing. But I don't know whether your stored process has parameters or what your stored process is trying to accomplish. At this point, you might need some debugging help from Tech Support. The problem, it seems to me, is that your stored process might need some fine turning or other programming logic in order to return either a manageable report result (from PROC PRINT) or a big data set (which can then be opened in Excel).

Sorry for going on so long. I hope this helps.
cynthia

[Cynthia_sas]

OK, I'm going to reply to my own post.

The whole posting title "Export to excel on a local PC" is also inaccurate, in so far as my last posting. The idea of NEEDING to EXPORT from SAS to Excel is still an accurate concept when we're talking about "standalone" SAS or "standalone" EG. But, when you are operating in the new context of the Enterprise Intelligence Platform, the whole idea of OPENING a SAS dataset directly in Excel is different from an EXPORT.

When I do an EXPORT, via a PROC EXPORT step or by right-clicking on a dataset and choosing EXPORT, I am actually asking for a file (SAS data set) to be converted from one form to another (Excel workbook/worksheet). The end result is that my SAS input file still exists, but I also have a copy of the data in Excel proprietary form and the EXPORT operation allows me to control that conversion process.

When I use the SAS Add-in for Microsoft Office, and I am working inside Excel, when I open a SAS data source in Excel, there is "technically" no EXPORT taking place. The fact that you can ask SAS to deliver the data rows in chunks proves that no EXPORT is taking place -- the retrieval of the records is dynamic -- a "visit" back to the data source happens every time a new chunk of records needs to be retrieved.

In addition, when we are talking about RESULTS or RESULT files, again, this is NOT an EXPORT to Excel. In BASE SAS or "standalone" SAS, I can create an HTML file using ODS. I can choose to open that HTML file with a browser or with Microsoft Word or with Microsoft Excel or with Macromedia Dreamweaver.

When I am working in Excel, within the context of the Enterprise Intelligence Platform, and request a stored process to run, my stored process can be executed FROM a variety of client applications -- therefore I am NOT creating a file, on my local drive that I could open with 4 different viewers. Imagine that your client application, like Excel is opening a pipeline to the stored process server -- when your stored process finishes executing, the results come down that pipeline. The results are aimed at that single client application, if you have selected STREAMING output for your stored process. If you create a PERMANENT package result, then you would be creating a package that could possibly be "unpacked" and subsequently opened with different viewers. But that's a whole 'nother topic.

Technically speaking, my stored process -- if it is running a PROC PRINT -- is not doing an EXPORT to Web Report Studio, or an EXPORT to PowerPoint or an EXPORT to the Information Delivery Portal or an EXPORT to Excel. I am creating a results file using PROC PRINT and the result file will be in a certain form (for Excel -- either SASReport XML or HTML or CSV) -- Excel knows how to open HTML or CSV files. SAS knows how to translate the SASReport XML into a form that Excel can read or receive.

OK, I think I'm way past 2 cents on this posting.

cynthia

[deleted_user]

"2 cents": as always Cynthia, you've paid the Florida State tax, the California energy tax, the U.K Goods and Services Tax and the Australian Value Added Tax as well for us.

I don't think an apology is necessary. I note that sometimes you refer to earlier threads in replies, and wonder whether you are searching for something you know you have answered, or whether there is a FAQ somewhere that I haven't found.

At the risk of suggesting these forums grow bigger than the venue for SAS Global Forum 2008 , is there any thought of creating such a tab? I have no idea how it might be populated, except perhaps from manual review, but the explanations on the Add-in have been thorough and well considered and having them preserved for ready reference seems to me to be a good idea.

Kind regards

David

[Cynthia_sas]

Thank you, David, for the list of taxes that I've paid with my explanation! (And for the compliment of saying that the response was thorough and well considered.)

Somebody joked today that I could easily talk and teach about ODS and Report Writing and Stored Processes for 5 days. And, sigh, it is true. You know, some folk are born to ride and some folk are born to run -- those are not my folk. I'm with the group of folk that were born to explain.

;-)
cynthia

[SP_RANDE]

Cynthia,
You were really wonderful explaining the background of what is what and what does what. I know the subject title is seems to be little different as I just copied this title from the previous ticket which was posted by one of our friends where as the same add-in matter was discussed.

I know this topic is going little further but would like to address the following items..
For your explanation and comparing to what I am trying to achieve from my stored process is exactly same:

1. I do have many imput parameters in my stored process. I had first tried proc print to print the results into excel. When the results are too big to print then the problem occurs. In this case I did not create a permenant dataset as it was not necessary while doing proc print.

2. As you recommended create a permanent dataset and then register metadata and then can access the dataset from excel. This is what I have been trying to get this done. In this process I know how to register the metadata n import those datasets thru Management console.

But in this case, after an user ran the stored process, user should depend on a person who can register the dataset into metadata for further access thru Excel. I am trying to get this using proc metalib to replicate the same process.
Here I 'm again facing some issues with unknown errors 'PMTA Library' etc..
Another ticket was posted by me long back on this error and I am glad for your reply to that to contact TechSupport for further help on that.
This error occurs from proc metalib and no work around now in the development unfortunately for EG 4.1 and solutions expected in the future versions.

So, Can I ask now is there any other procedure that we can use to register the dataset into metadata library in a programatical way not doing a manual process..?

Anyone had faced the same error while trying this procedure? if so which way we can use this proc metalib in a better manner not to get any unexpected errors.

Thanks!
Raja

[Cynthia_sas]

Once you are into questions about PROC METALIB, your best bet is to contact Tech Support. I am a bit skittish about running PROC METALIB on the fly to define working tables.

Perhaps an intermediate alternative would be to ask Tech Support to show you how to write a stored process that would produce a permanent package that included an HTML file AND/OR a SAS data set. Your choice with a permanent package is to publish it to a webDAV repository or to publish it to an operating system location from which the users could fetch it.

Tech Support can help you with this.

cynthia