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 ??
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:
rc = stpsrv_header('Content-type','application/vnd.ms-excel;');
rc = stpsrv_header("Content-disposition","attachment; filename=PPR_Temp.xls");
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:
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.
Working with a collegue we found the following code worked but not quite sure why...
rc = stpsrv_header('Content-type','application/vnd.ms-excel');
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:
ods CSVALL body=_webout;
proc print data =work.test_results_for_XML label noobs;
ODS CSVALL CLOSE;
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.