07-25-2013 05:28 AM
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!
07-25-2013 08:37 AM
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.
07-25-2013 08:45 AM
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"
07-25-2013 08:49 AM
I suggest looking at using ODS ExcelXP tagsets. Some links you may find useful are:
Hope this helps.
07-25-2013 01:48 PM
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.
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];
9.ODS TAGSETS.EXCELP CLOSE;
10. ODS LISTING;
07-25-2013 03:19 PM
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.
07-25-2013 05:24 PM
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";
proc export data = your_dataN
outfile = "your_path\your_excel_file_name.xlsx"
dbms = excel replace;
sheet = "sheet_with_dataN";
07-25-2013 05:48 PM
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.
07-25-2013 10:23 PM
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.
P.S. Would be actually nice to hear from the person who posted the question in first place.
07-26-2013 10:28 AM
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.
08-01-2013 02:43 PM
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..
07-17-2015 12:50 PM
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?
07-25-2013 05:59 PM
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.
*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.
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/
07-25-2013 06:15 PM
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
07-25-2013 07:02 PM
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.