Help using Base SAS procedures

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

Reply
Occasional Contributor
Posts: 8

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

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

Trusted Advisor
Posts: 2,116

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

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.

Contributor
Posts: 38

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

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;

Trusted Advisor
Posts: 1,322

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

Hi Jacopo,

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

Hope this helps.

Cheers,

MIchelle

Occasional Contributor
Posts: 14

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

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,

R

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;

Occasional Contributor
Posts: 14

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

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

Contributor
Posts: 21

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

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!

Occasional Contributor
Posts: 14

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

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

Contributor
Posts: 21

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

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.

Occasional Contributor
Posts: 14

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

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

Occasional Contributor
Posts: 8

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

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

New Contributor
Posts: 3

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

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?

Trusted Advisor
Posts: 1,322

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

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/

Occasional Contributor
Posts: 14

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

Posted in reply to MichelleHomes

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

Super User
Posts: 3,261

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

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.

Ask a Question
Discussion stats
  • 18 replies
  • 14831 views
  • 2 likes
  • 11 in conversation