BookmarkSubscribeRSS Feed
deleted_user
Not applicable
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;
3 REPLIES 3
Cynthia_sas
SAS Super FREQ
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
deleted_user
Not applicable
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;
Cynthia_sas
SAS Super FREQ
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

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 3 replies
  • 2041 views
  • 0 likes
  • 2 in conversation