topic Re: Excel and Stored process in Developers

Excel and Stored process

deleted_user — Mon, 02 Jul 2007 12:08:00 GMT

Our stored process is a Unix server, I need to produce a CSV or Excel file from my stored process so that it can by used by other tools using the code below produces a file that Excel can read but not out other software. Is there a way to produce a "pure" csv or Excel file ??

Data _null_;
rc = stpsrv_header('Content-type','application/vnd.ms-excel;');
rc = stpsrv_header("Content-disposition","attachment; filename=PPR_Temp.xls");
run;
%let _odsdest=tagsets.msoffice2k;

Re: Excel and Stored process

Cynthia_sas — Mon, 02 Jul 2007 15:27:50 GMT

Hi:
The answer REALLY depends on what client application your end-users are going to use to surface the stored process results.

For example...if they will be using the SAS Add-in for Microsoft Office, then THEY can select CSV results from the SAS --> Options menu inside Excel. You don't have to do anything to your stored process if you allow them to make this choice. Ditto, if you write a "general" stored process and they select HTML as the result type -- then your stored process will come back as HTML -- using style information, such as font and colors -- if you allow them to make this choice.

You said that "using the code below produces a file that Excel can read but not our other software" -- what is the other software that you are using? You can return many different types of files with Stored Processes. This code should allow you to return a CSV file, assuming that the receiving software knows how to deal with a content-type header:
[pre]
Data _null_;
rc = stpsrv_header('Content-type','application/vnd.ms-excel;');
rc = stpsrv_header("Content-disposition","attachment; filename=PPR_Temp.xls");
run;

%let _odsdest=tagsets.csv;
*ProcessBody;
%stpbegin;
proc print data=sashelp.class;
run;
%stpend;

** OR an alternate method to code the stored process would be: ;

Data _null_;
rc = stpsrv_header('Content-type','application/vnd.ms-excel;');
rc = stpsrv_header("Content-disposition","attachment; filename=PPR_Temp.xls");
run;

ods csv file=_webout;
proc print data=sashelp.class;
run;
ods csv close;
[/pre]

Other destination values that you can use are:
%let _odsdest = tagsets.excelxp; /* Microsoft Spreadsheet ML XML results */
%let _odsdest = tagsets.msoffice2k; /* Microsoft HTML results */
%let _odsdest = tagsets.HTML3; /* W3C HTML 3.2 compliant tags */
But the chances are good that ONLY Excel will open the ExcelXP and MSOFFICE2K files AND, your software mayor may not deal with the HTTP content-type header correctly. If you used the tagsets.HTML3 destination AND your other software knows how to open W3C-compliant HTML files, this may be your best bet. There is a list of content-type headers associated with CSV files (http://filext.com/file-extension/CSV) If your application does not like vnd.ms-excel as shown above,perhaps it wants to have one of these MIME type headers:
[pre]
text/comma-separated-values
text/csv
application/csv
application/excel
application/vnd.ms-excel
application/vnd.msexcel
[/pre]

You could also use PROC EXPORT, but as you can read from several previous threads, this generally will write the XLS file on the server machine -- NOT on the end-user's local machine.

Your best bet for a definitive response, given your particular configuration and the other software you need to use to open the stored process results file, might be to contact Tech Support: http://support.sas.com/techsup/contact/index.html

cynthia

Re: Excel and Stored process

deleted_user — Tue, 03 Jul 2007 15:14:26 GMT

Thanks
Working with a collegue we found the following code worked but not quite sure why...
data _null_;
rc = stpsrv_header('Content-type','application/vnd.ms-excel');
rc =
stpsrv_header('Content-disposition','attachment; filename=temp.csv');
run;

%let _RESULT=PACKAGE;
%let _ODSOPTIONS = %str(body="~/body.HTML") ;
%let _odsdest=tagsets.msoffice2k;
%stpbegin ;
ods listing close;
/**other code ; **/
%stpend;
ods CSVALL body=_webout;
proc print data =work.test_results_for_XML label noobs;
run;
ODS CSV CLOSE;

Re: Excel and Stored process

Cynthia_sas — Tue, 03 Jul 2007 15:47:52 GMT

Ah, that's good. I generally do not change the result type with an override -- I define it when I set the metadata for the stored process. You can read about changing _RESULT here:
http://support.sas.com/rnd/itech/doc9/dev_guide/stprocess/result.html

And this is a list of all the reserved macro variables, where you can read about _ODSOPTIONS:
http://support.sas.com/rnd/itech/doc9/dev_guide/stprocess/reserved.html

for most stored processes, I would not bother with overriding the value for the body= or file= file name -- but it makes some sense to name it for package results -- otherwise I think you get a default name of "main.htm"

FYI...when you open a destination like ODS CSVALL, you have to issue a close for the same destination:
[pre]
ods CSVALL body=_webout;
proc print data =work.test_results_for_XML label noobs;
run;
ODS CSVALL CLOSE;
[/pre]
I'm assuming that you had a typo in your posting when you indicated that you used ODS CSV CLOSE.

When I use the Stored Process Web Application to launch a Stored Process to return CSV results from a URL, I do NOT need to use overrides for _RESULT or _ODSOPTIONS -- my streaming results come back correctly; however, Excel does get launched. If you do NOT want Excel to launch, then I can see where making a Package might suit your needs better.

cynthia