topic Export data from SAS EG to Excel in SAS Enterprise Guide

Export data from SAS EG to Excel

DrZenith — Mon, 05 Dec 2011 21:12:22 GMT

Hey guys....

I have SAS EG 4.1 with the following licenses, though I don't have access to BASE SAS

---Base Product 30JUL2012

---SAS/STAT 30JUL2012

---SAS/GRAPH 30JUL2012

---SAS/ETS 30JUL2012

---SAS/CONNECT 30JUL2012

---SAS Enterprise Miner 30JUL2012

---SAS Integration Technologies 30JUL2012

---Enterprise Miner Server 30JUL2012

---Enterprise Miner Thin Client 30JUL2012

---SAS/ACCESS Interface to Teradata 30JUL2012

---PRODNUM248 30JUL2012

I have some PROC SQL statements in a SAS file...I want the results to exported to Excel(2003 version would also do). I tried Googling, but couldnt relate to what I was trying to do in EG.

Hoping for some positive response!!

Export data from SAS EG to Excel

TriciaA — Mon, 05 Dec 2011 21:25:33 GMT

Here's one of many methods to export a SAS DSN to EXCEL. I am assuming that you can access your desktop PC in this example. However,

ods html file="C:/temp/my_new_output.xls"

style=minimal;

proc sql;

select * from sashelp.shoes;

quit;

ods html close;

You can also use PROC Export (File > Export), a SAS Tagset (such as ExcelXP), and probably some ways I have forgotten.

Here's a good paper: http://www.nesug.org/proceedings/nesug04/io/io09.pdf

Tricia

SAS BI-NOTES Blog

Export data from SAS EG to Excel

RonO — Mon, 05 Dec 2011 21:27:08 GMT

Hey Dr. ! I think you need a SAS/ACCESS Interface to ODBC for anything MS-related. I use a 64-bit Windows 2003 server so I need the SAS/ACCESS interface to PC Files as well (64-bit vs. 32-bit files).

Export data from SAS EG to Excel

DrZenith — Mon, 05 Dec 2011 21:48:14 GMT

Yeah..Thats seems to be the issue..I don't have sas/access. When i try to run ODS HTMl or ODS CSv...it says "Insufficient authorization to access /opt/apps/sas/config/Lev1/SASApp"

What next then???

Export data from SAS EG to Excel

TriciaA — Mon, 05 Dec 2011 21:57:32 GMT

Since it says you cannot authorize that directory - it means you are probably not allowed to write to your desktop.

You system admin needs to provide a place/permission to write the Excel file.

Did you try the File > Export method? Probably a long shot - but you might be able to see where you are allowed to write files to.

Also do you have the MS Excel Add-IN? You can access the SAS Folders from Excel.

Export data from SAS EG to Excel

twocanbazza — Tue, 06 Dec 2011 20:36:47 GMT

As for your HTML permissions error

Give a proper location that you have access to for your HTML file, the error you see is because you can't write to the default location that you mention

Export data from SAS EG to Excel

TomKari — Wed, 07 Dec 2011 02:48:10 GMT

When you use Enterprise Guide, behind the scenes it is using SAS. It either uses SAS on your PC, or on a remote server.

In your case, the directory tree that it is trying to write to looks like Unix syntax. I suspect your behind-the-scenes SAS code is running on a Unix server.

This isn't a problem, but you have to contact your system administrator, and find out where you are supposed to write files on your Unix server. Once you know this, specify it in your "file=" parameter in the response given by TriciaA. You don't need SAS/Access to use ODS. (something like "file=/home/drzenith/myexcelfile.xls")

However, once you write it to a file on your Unix server, you then need to get it to your PC.

Export data from SAS EG to Excel

DrZenith — Thu, 08 Dec 2011 12:33:57 GMT

That's the exact case, it's on a remote server. Thanks TriciaA and Tomkari.

Re: Export data from SAS EG to Excel

broz — Fri, 09 Dec 2011 16:09:23 GMT

Hi Dr.Z

You might ask your UNIX admin to set up a SAMBA drive (see http://www.samba.org/) so that you can write there and access it direct from your PC (see this note http://support.sas.com/kb/17/119.html) - Its nice, because once the default path for the EGuide "FILES" is set to that SAMBA drive (See http://support.sas.com/kb/16/364.html) you can write/read/export/import stuff out there, and your windows system can be setup to access that drive too. Then you can use lots of nice tricks to get clean excel output from ODS, such as http://support.sas.com/kb/24/823.html

Cheers