Desktop productivity for business analysts and programmers

Export data from SAS EG to Excel

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 12
Accepted Solution

Export data from SAS EG to Excel

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


Accepted Solutions
Solution
‎12-06-2011 09:48 PM
Trusted Advisor
Posts: 1,052

Export data from SAS EG to Excel

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.

View solution in original post


All Replies
Contributor
Posts: 40

Export data from SAS EG to Excel

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

New Contributor
Posts: 3

Export data from SAS EG to Excel

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

Occasional Contributor
Posts: 12

Export data from SAS EG to Excel

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

Contributor
Posts: 40

Export data from SAS EG to Excel

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.

Super Contributor
Posts: 356

Export data from SAS EG to Excel

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

Solution
‎12-06-2011 09:48 PM
Trusted Advisor
Posts: 1,052

Export data from SAS EG to Excel

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.

Occasional Contributor
Posts: 12

Export data from SAS EG to Excel

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

New Contributor
Posts: 2

Re: Export data from SAS EG to Excel

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

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 8 replies
  • 4379 views
  • 3 likes
  • 6 in conversation