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 🙂
@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)?
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:
27
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 ?
-> 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.
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
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)
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;
25
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;
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.
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
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 16. Read more here about why you should contribute and what is in it for you!
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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: