topic Re: How to export a sas (EG 5.1) table to excel2007 using code (and not a wizard) in SAS Procedures

How to export a sas (EG 5.1) table to excel2007 using code (and not a wizard)

j4copo — Thu, 25 Jul 2013 09:28:02 GMT

Hi,

i would need to export many sas tables into different sheets of a unique excel workbook 2007 .xlsx. In enterprise guide 5.1 there is a wizard which allows me to partially do that: i cannot send tables to specific sheets of a unique workbook tough. but the worst thing is that i need to save many tables and possibly integrate this step in a macro so that i need to code it And not using a wizard. Thanks for any help!

jacopo

Re: How to export a sas (EG 5.1) table to excel2007 using code (and not a wizard)

Doc_Duke — Thu, 25 Jul 2013 12:37:47 GMT

you will have to write the code then. Check out the manual, SAS/Access for PC Files.

I should add that if you search these forums for "excel" you will find a lot of examples showing what you want to do.

Re: How to export a sas (EG 5.1) table to excel2007 using code (and not a wizard)

fred_major — Thu, 25 Jul 2013 12:45:40 GMT

Hi,

Use something like this.

/* Create Archive of your data */

proc export data= Your dataset
            outfile= " Put the path or location where you want the file to go"
            dbms=excel replace;
       sheet="Totals";
run;

Re: How to export a sas (EG 5.1) table to excel2007 using code (and not a wizard)

MichelleHomes — Thu, 25 Jul 2013 12:49:17 GMT

Hi Jacopo,

I suggest looking at using ODS ExcelXP tagsets. Some links you may find useful are:

Hope this helps.

Cheers,

MIchelle

Re: How to export a sas (EG 5.1) table to excel2007 using code (and not a wizard)

rachel — Thu, 25 Jul 2013 17:48:08 GMT

Hi,

You can create an excel workbook with multiple sheets using eg.

The important thing is the file name. Find out the name of the location

where you have permission on the SAS server. You can run something in EG and read

the log, and it will diplay the file path.

The code below will create one excel workbook with one sheet, if you want multiple sheets

then after the run statement type QUIT; and repeat all steps but not #1 and not #3. Because you only need one file statement to create a workbook, but you need a to repeat all the steps to created multiple sheets. After you run it, you click on results and youll get a promp asking you if you want to open the file, and it may give you a warning, but just go ahead and click yes, then an excel icon will appear and pulsate, and after you click on the icon, your output will open in a nice formatted excel spreadsheet. You can add all kinds of options, but you can look those up yourself.

I am submitting a paper to SUGI, it seems that there are a lot of people who have to use EG, but dont know that you can simulate the good old proc export like we use too in BASE SAS.

Best,

Then

Here is the code:

1.ods _all_ close;

2.ODS MARKUP TAGSET=TAGSETS.EXCELXP

3.file='/var/opt/data/userdata/--->>>...type your name/userid here...<---./file_name.xls'

4.style = statistical

5.options( put any option you want)
6.proc print data=your data name and options;

7. var your_var_name/style= [ your options];

8.run;

9.ODS TAGSETS.EXCELP CLOSE;

10. ODS LISTING;

Re: How to export a sas (EG 5.1) table to excel2007 using code (and not a wizard)

rachel — Thu, 25 Jul 2013 19:19:12 GMT

Ok...so what exactly do you need to do?

You can either use the code above and write a program in EG, or you can use EG and after generating the results click on the tab "Export" its beteween "Refresh" and "Send To" after you click select the second option "Export SAS Report as a Step in the Project" then youll get a pop up window with saving options.

Then you can open it from excel. Later you can combine all those tables into one workbook using excel commands.

Best,

-R

Re: How to export a sas (EG 5.1) table to excel2007 using code (and not a wizard)

Marina — Thu, 25 Jul 2013 21:24:32 GMT

If you need to export data to Excel, export it. You don't need to use ODS and tagsets.

As fred.major already suggested, use proc export. If you want multiple datasets exported in the same Excel file, outfile name should be the same for all exported datasets. And you can easy convert this code to macro.

proc export data = your_data1

outfile = "your_path\your_excel_file_name.xlsx"

dbms = excel replace;

sheet = "sheet_with_data1";

run;

...

proc export data = your_dataN

outfile = "your_path\your_excel_file_name.xlsx"

dbms = excel replace;

sheet = "sheet_with_dataN";

run;

Good luck!

Re: How to export a sas (EG 5.1) table to excel2007 using code (and not a wizard)

rachel — Thu, 25 Jul 2013 21:48:22 GMT

Marina,

The point is that proc export/proc import doesnt work in SAS EG.

I thought that the questionsj4copo posted on July 25th is

How to export sas eg table into excel?

You cant use proc export or proc import in SAS EG.

Thats why the tagsets.

-R

Re: How to export a sas (EG 5.1) table to excel2007 using code (and not a wizard)

MichelleHomes — Thu, 25 Jul 2013 21:59:35 GMT

As you will find there are many ways to do things in SAS. The reason why I suggested ODS ExcelXP tagsets is because this method does not rely on having SAS/Access to PC File Formats licensed. Using PROC EXPORT with the excel engine does. If you do have SAS/Access to PC File Formats licensed then another approach is to use the Excel libname engine. This is where you set up a library reference that points to an excel workbook and then you can reference your worksheets as if they are SAS datasets.

For example:

*The libname statement may vary depending on the bit level of your operating system and your SAS server. See article below;
libname myxls excel "your_path\name_of_excel_file.xlsx"; 
proc copy in=work.yourSASdataset out=myxls.nameOfworksheet;
run;
libname myxls close;

Or alternatively as you create the dataset you simply create it in the spreadsheet without having to create a SAS dataset as well.

For example:

libname myxls excel "your_path\name_of_excel_file.xlsx";
data myxls.dataset1; *You are writing to the worksheet in the spreadsheet directly;
     set work.someOthertable;
     ..... SAS code...
run;
libname myxls close;

Chris Hemedinger wrote a few post about working with excel that you may be interested to read: http://blogs.sas.com/content/sasdummy/2012/02/11/export-excel-methods/

Re: How to export a sas (EG 5.1) table to excel2007 using code (and not a wizard)

rachel — Thu, 25 Jul 2013 22:15:11 GMT

I tried but the code you posted above in sas eg produced an error

thats why i use tagset with a file name

This doesnt work when you have sas eg run on a remote server

-R

Re: How to export a sas (EG 5.1) table to excel2007 using code (and not a wizard)

SASKiwi — Thu, 25 Jul 2013 23:02:13 GMT

Proc EXPORT to Excel works fine in an EG / remote SAS server environment as long as SAS/Access to PC File Formats is licensed (check usiing PROC SETINIT), and required MS Office components are installed. We use it all the time in this environment. The Excel LIBNAME also works.

You can use the ODS approach if you don't have SAS/Access to PC File Formats but you are limited to creating a new spreadsheet from scratch with all required tabs each time you export - you can't add to an existing spreadsheet.

Re: How to export a sas (EG 5.1) table to excel2007 using code (and not a wizard)

Marina — Fri, 26 Jul 2013 02:23:17 GMT

Rachel,

Proc EXPORT works in EG just fine.

Moreover, the EG wizard allowing to export to Excel is mentioned, so I would assume the SAS/Access to PC File Format is present. Also, the original question was how to export in Excel 2007 (.xlsxI). And considering the possibility to use code in macro, I think using proc EXPORT with REPLACE would be the way to do it.

The Excel LIBNAME works too, but to use it in would be time-consuming.

Best regards,

Marina

P.S. Would be actually nice to hear from the person who posted the question in first place.

Re: How to export a sas (EG 5.1) table to excel2007 using code (and not a wizard)

rachel — Fri, 26 Jul 2013 14:28:03 GMT

Marina,

Proc export doesnt work in EG when you have your data on a server.

There is an error message.

ERROR; DBMS type EXCEL not valid for export.

I even called SAS and put it a ticket about this in the past and they confirmed.

I know it works in BASE SAS because I used it, but not in EG with a remote server.

-R

Re: How to export a sas (EG 5.1) table to excel2007 using code (and not a wizard)

j4copo — Thu, 01 Aug 2013 18:43:58 GMT

Rachel you got the point. i think the all problem could be overcome by enabling SAS/Access to PC File Formats as Saskiwi wrote. I do not know if this is possible, I hope the IT dept will help me out, thanks everyone for your replies..

Re: How to export a sas (EG 5.1) table to excel2007 using code (and not a wizard)

anonymous_user — Mon, 18 Nov 2013 08:10:06 GMT

hi ,

i find the best way to export to an excel format without a wizard is by using ods tagsets.ExcelXP.

this works on a linux based server and you get a excel output in .xls format.

here is an example (fill in the XXXXX and the path) :

*************************************************

ods tagsets.ExcelXP options(embedded_titles='Yes')
file="/file_path/file_name.xls" style=Sasweb
options(Autofit_height='yes' Absolute_Column_Width='3,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10,10' autofilter='all' );

title "XXXXX";
ods tagsets.ExcelXP options (sheet_name = "XXXXXX") ;
proc print data=XXXXXXX label;
;
label
;
run;

title ;
ods _all_ close;

*****************************************************

Re: How to export a sas (EG 5.1) table to excel2007 using code (and not a wizard)

j4copo — Wed, 17 Dec 2014 09:46:50 GMT

Hi guys,

thanks a lot for all the help..

I finally found a way to export an excel file using code and no wizard. I used ods tagsets.ExcelXP, but the created files are saved in a temporary folder in my pc, then it is not all good..

Today I got the add-in PC Files installed on my machine and I am making new tries..

the problem seems to be that whenever i try to fix a local path (C:\desktop ... ) sas automatically append at the beginning of this path the following:

/sas/sw/config/Lev1/SASApp/ and consequently my path is not correct. seems like i am near to the solution but i'm sure there is a much simpler way to do it now that I have this PC Files installed on, maybe some initial setting modification ?

I keep on making tries.

thanks for any help,

Jacopo

Re: How to export a sas (EG 5.1) table to excel2007 using code (and not a wizard)

Patrick — Wed, 17 Dec 2014 10:08:59 GMT

This is a UNIX path and it's likely where your SAS session on the server gets started from: /sas/sw/config/Lev1/SASApp/

This is a Windows path which I assume relates to your local workstation where SAS EG is installed: C:\desktop ..

SAS EG is the client. The EG Import & Export Wizards allow you to do stuff on the client side. Everything else runs on the server side. Writing Excel output via SAS code executes on the SAS server - and it must be a path to a directory known to the Server. And that's why c:\desktop... will not work.

The following link should be helpful to you

How do I export from SAS to Excel files: Let me count the ways - The SAS Dummy

Re: How to export a sas (EG 5.1) table to excel2007 using code (and not a wizard)

JamBar — Fri, 17 Jul 2015 16:50:36 GMT

I'm transitioning from PC-SAS to SAS EG. It seems that I'm getting the same error. I then get an error "insufficient authorization to access /sas/bin..." What is the workaround for SAS EG? Also, is there an alternative SAS program that's more closely related to PC-SAS?

Re: How to export a sas (EG 5.1) table to excel2007 using code (and not a wizard)

jakarman — Fri, 17 Jul 2015 17:47:05 GMT

That error is because you are using an Unix server not Windows and the current directory is set wrong by SAS and your IT staff did not correct that. 50345 - Changing the current working directory for the SAS® Workspace Server

You can still use the proc export creating an excel file on UNIX SAS/ACCESS(R) 9.4 Interface to PC Files: Reference, Third Edition The trick is using the XLSX option for the DBMS name. See the table what is working on Windows and what on Unix.

The XLSX option for the libname is new for 9.4 the export/import already do have them with 9.

The ods tagset can generate a xml-file that is what it does and Excel can read that. The compression of the data can be problematic (sizing network).