Hi:
It was my understanding that you could not automatically populate multiple worksheets from a single stored process run. But, for example, you can open Excel, start a stored process -- let's say with an input parameter of Germany -- and you can put Germany's output on one worksheet. If you run that same stored process -- a second time -- you can, on the popup window, choose to insert the results from the second run into a new worksheet -- this second time, maybe, with an input parameter of France -- and you will put France's output on a second worksheet in the same workbook.
In the SAS Add-in for Microsoft Office, you can choose (via a popup window) whether stored process results will populate the existing worksheet, a new worksheet or a new workbook. But I do not believe that there is a way for your stored process to "interact" with this popup window in Excel.
I would encourage you to rethink whether you really want to return that many stored process result rows to Excel. Is it possible that you might need to refine filters? Users generally will not look through or page through 1000 rows of output, much less 65,000 observations.
If your stored process is returning large results just so you can do further analysis with the results in Excel, you may want to consider creating a result dataset with the stored process, and then point the SAS tasks and wizards to the big result dataset. You can just POINT to the dataset with the Active Data Source choice (choose Active Data from the SAS menu -- NOT Open Data Source --> Into Worksheet) and then run your analysis or task against the data. The physical data set does NOT have to be loaded into Excel in order for the tasks and wizards to work.
The reason that the Tech Support notes refer to SAS/Access for PC File Formats is that SAS/Access, via the Libname Engine or PROC EXPORT allows you to export SAS data to a single Excel worksheet or to multiple Excel worksheets. In a similar fashion, if you had SAS/Access for DB2 and had all the correct permissions, you could run a SAS job that might update a DB2 table with SAS data. But, you would have to look at the final DB2 table using DB2 utilities OR by issuing a PROC PRINT on the DB2 table and getting the results in a SAS report file. It is unlikely that you would ever update a DB2 table with the results from PROC MEANS or PROC PRINT, for example. You would always be updating the DB2 table with SAS data if you were using SAS/Access for DB2. If that scenario makes sense when talking about DB2, none of that philosophy changes when you talk about using SAS/Access for Excel.
Let's look at a sample stored process, named simple_lib_SP:
[pre]
*ProcessBody;
libname mywb "{path-on-server-with-write-access}\test_workbook.xls" version=2002;
data mywb.Asia;
set sashelp.shoes;
where region = 'Asia';
run;
data mywb.Canada;
set sashelp.shoes;
where region = 'Canada';
run;
libname mywb clear;
[/pre]
...and register it in the Metadata to run on the Workspace Server with an output type of NONE (appropriate for creating files or data sets). Then, if I have write access to the directory on the server, when I run the stored process (in Excel), I get this message in Excel
[pre]
"simple_lib_SP" executed at mm/dd/yyyy hh:mm:ss PM.
[/pre]
Then, I can use File --> Open on the Excel menu to navigate to the server location and open the multi-worksheet Excel file that the stored process just created. Or, if the server location is not accessible to Excel, I may have to start up an FTP utility and download the .XLS file to a file location that I can use with Excel.
There is a difference between running PROC PRINT in a stored process (which produces results that can be sent to a single Excel worksheet automatically using the BI Platform and the SAS Add-in) and using SAS/Access ( which creates a true, "binary" Excel file). You can use the SAS Add-in to get PROC PRINT (or PROC MEANS or PROC FREQ...) results into Excel. But to my knowledge you cannot use the SAS Add-in to automatically receive an Excel file created with SAS/Access for PC File Formats -- you can create the Excel file on the server with a stored process and then you can open the file with Excel. But in this scenario, it would not matter how you submitted the stored process -- you could submit the stored process from PowerPoint or EG -- and it would run and create the .XLS file on the server file location.
For more help with this request, you might consider contacting Tech Support, as they can help you figure out how to either refine your stored process so it returns less than 65K rows or help you figure out how to use SAS/Access for PC File Formats on UNIX to connect to a server where you can create Excel files -- and how to do it in a stored process.
cynthia
