Your SAS programs, embedded in web apps and elsewhere

dynamically export the STP output to multiple sheets in an Excel workbook

Reply
Occasional Contributor
Posts: 7

dynamically export the STP output to multiple sheets in an Excel workbook

Hi,
I have an STP which generates an output of more than 65k rows and the endusers are running it in Excel by means of the SAS Add-in. How can i make the STP automatically distribute it's output to multiple sheets when it reaches the maximum rows in Excel by means of SAS Add-in? I have read several SN's but all referred to SAS/ACCESS license for Windows Platform and we have an Unix Platform SAS/ACCESS license.
SAS Super FREQ
Posts: 8,744

Re: dynamically export the STP output to multiple sheets in an Excel workbook

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
Occasional Contributor
Posts: 7

Re: dynamically export the STP output to multiple sheets in an Excel workbook

Hi Cynthia,
Thanks for your reply. I was testing it out also with the Libname Engine and Proc Export but with both it was not working in order for me to create the different sheets in one workbook. The data is huge since it is detailed information based upon different category types and I wanted to create multiple sheets per category code or automatically create sheets when it reaches the max of 65k. I have also contacted Tech Support but their response was the same what I could find on the SN's and all of them are talking about Windows and not Unix.
SAS Super FREQ
Posts: 8,744

Re: dynamically export the STP output to multiple sheets in an Excel workbook

Hi:
Take a look at this SUGI paper:
http://www2.sas.com/proceedings/sugi28/156-28.pdf

it shows this kind of libname engine statement for Unix:
[pre]
libname x pcfiles type=excel port=524
server=myserver path=”c:\my excel file.xls”;
[/pre]

I believe it is this kind of libname statement that you need with UNIX. As I said, your stored process using the above technique would create the multi-sheet workbook and then your user would have to explicitly use the File-->Open step inside Excel to open the file.

I think you should still stick with with Tech Support for help-- since you can point to this SUGI paper as an example of how SAS/Access on Unix is supposed to work.

cynthia
Occasional Contributor
Posts: 7

Re: dynamically export the STP output to multiple sheets in an Excel workbook

Thanks Cynthia.
I will check this out.
Ask a Question
Discussion stats
  • 4 replies
  • 322 views
  • 0 likes
  • 2 in conversation