BookmarkSubscribeRSS Feed
v9dduhan
Fluorite | Level 6

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 🙂

8 REPLIES 8
Kurt_Bremser
Super User

@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)?

v9dduhan
Fluorite | Level 6

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 ?

Kurt_Bremser
Super User

->  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.

 

v9dduhan
Fluorite | Level 6

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

Kurt_Bremser
Super User

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)

 

v9dduhan
Fluorite | Level 6

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;

 

Vince_SAS
Rhodochrosite | Level 12

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

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
  • 8 replies
  • 1329 views
  • 3 likes
  • 3 in conversation