topic Get SAS Stored procedure output in excel automatically while running from web portal in Developers

Get SAS Stored procedure output in excel automatically while running from web portal

v9dduhan — Tue, 17 Jan 2017 06:10:33 GMT

Hi,

I'm developing sas stored procedures to get attached in web portal to display data automatically in Excel. But i am getting error while running this code. Can anyone help me with the code which i can include in my stored procedure to get output automatically in excel ?

Thanks,

Good luck to me 🙂

Re: Get SAS Stored procedure output in excel automatically while running from web portal

Kurt_Bremser — Tue, 17 Jan 2017 07:47:00 GMT

@v9dduhan wrote:

Hi,

But i am getting error while running this code.

And a sack of rice dropped in China.

You need to be MUCH more specific to get help.

- log (excerpt) with code and ERROR(s)

- what are you trying to achieve in detail?

(Loading in Excel through Add-In, automatic creation of Excel file, ...)

- what is your SAS setup (1-2-3 tier, involved operating systems)?

Re: Get SAS Stored procedure output in excel automatically while running from web portal

v9dduhan — Tue, 17 Jan 2017 08:18:01 GMT

Here is code:

%global make;

%global type;

proc sql;

create table t1 as

select *

from sashelp.cars

where make = "&make."

and type = "&type.";

quit;

ods tagsets.ExcelXP file='c:\test1.xlsx';

proc report data=work.t1;

run;

This is error:

28 ods tagsets.ExcelXP file='c:\test1.xlsx';

NOTE: Writing TAGSETS.EXCELXP Body file: c:\test1.xlsx

ERROR: Insufficient authorization to access /sas/EBIServer/Lev2/SASApp_Prod/c:\test1.xlsx.

ERROR: No body file. TAGSETS.EXCELXP output will not be created.

29 proc report data=work.t1;

30 run;

I want to get output in temporary excel file on same system from which this stored procedure invoked.

Here i'm just testing using "C:" drive of my system, but this should be temporary file location.

Any suggession ?

Re: Get SAS Stored procedure output in excel automatically while running from web portal

Kurt_Bremser — Tue, 17 Jan 2017 08:26:37 GMT

-> ERROR: Insufficient authorization to access /sas/EBIServer/Lev2/SASApp_Prod/c:\test1.xlsx.

So you have at least a 2-tier SAS setup (server and client), and the server runs on UNIX.

The working directory for the SAS server process is /sas/EBIServer/Lev2/SASApp_Prod, and a normal user (or sassrv, which runs pooled servers like the stored process server) must not have write access there.

Necessary actions by you:

- define (or have defined) a location on the server where such temporary files can be written. Make sure that the users involved have write permisison there

- use UNIX style filename/pathname syntax. Look here: https://en.wikipedia.org/wiki/Unix_filesystem

- after creating the file, you need to download it to your PC. WinSCP is a tool for this. Or you create the STP in a way that it sends the ouput directly, using proper HTML code, so the browser knows what to do with the result.

Re: Get SAS Stored procedure output in excel automatically while running from web portal

v9dduhan — Tue, 17 Jan 2017 10:49:21 GMT

Thanks @Kurt_Bremser for making me understand the problem and structure. Can you please help with the code as well through which i can send output directly to location. I am not good in html coding, but i will try. can you give me general structure in stored procedure perspective which will send output to location.

Thank you so much

Re: Get SAS Stored procedure output in excel automatically while running from web portal

Kurt_Bremser — Tue, 17 Jan 2017 11:24:33 GMT

First of all, get a simple step running before trying your hand at stored processes.

One location that is surely writable by you is your UNIX home directory.

So modify your code like this:

ods tagsets.ExcelXP file='$HOME/test1.xml';

proc report data=work.t1;
run;

ods tagsets.excelxp close;

Once that step runs without ERRORs or WARNINGs, start WinSCP or similar, log on to the SAS server (using ypur SAS credentials), and you'll find the test1.xml.

(I used .xml because tagsets.excelxp creates a XML file, and Excel from 2010 on complains if the filename extension does not match the contents)

Re: Get SAS Stored procedure output in excel automatically while running from web portal

v9dduhan — Tue, 17 Jan 2017 11:43:02 GMT

Still getting error:

NOTE: The address space has used a maximum of 1148K below the line and 57760K above the line.

24 %let _odsdest=excel;

26 ods tagsets.ExcelXP file='$HOME/test1.xml';

NOTE: Writing TAGSETS.EXCELXP Body file: $HOME/test1.xml

ERROR: A component of /usr/lpp/SAS_R93M2/config/Lev2/SASApp_SYSA/$HOME is not a directory.

ERROR: No body file. TAGSETS.EXCELXP output will not be created.

27 title 'SASHELP.Class';

28 proc report data=work.t1;

29 run;

Re: Get SAS Stored procedure output in excel automatically while running from web portal

Kurt_Bremser — Tue, 17 Jan 2017 11:50:10 GMT

You have switched context

/usr/lpp/SAS_R93M2/config/Lev2/SASApp_SYSA/ points to a different directory than that in your first post.

That's not your EBIServer context.

Re: Get SAS Stored procedure output in excel automatically while running from web portal

Vince_SAS — Wed, 25 Jan 2017 18:07:43 GMT

You need to direct the output to the reserved _WEBOUT FILEREF. Review page 18 of this paper:

http://support.sas.com/resources/papers/proceedings15/SAS1700-2015.pdf

Then try something like this:

ods _all_ close;

%let RV=%sysfunc(appsrv_header(Content-type,application/vnd.ms-excel));
%let RV=%sysfunc(appsrv_header(Content-disposition,attachment; filename="t1.xml"));

ods tagsets.ExcelXP file=_webout;
  proc report data=work.t1 nowd; run; quit;
ods tagsets.ExcelXP close;

Vince DelGobbo

SAS R&D