Excel sheets through ODS

[Aman4SAS]

Hi ,

I am creating 7 reports with the help of Proc report. and now i want to make a excel with separate sheets for each report.

is it possible from ODS or any other way in SAS?

Please suggest.

Thanks

[RW9]

Yes, have a look at ods tagsets.excelxp.  You can do it with proc export, but you have more control over output with the tagset:

ods tagsets.excelxp file="...\tmp.xls";

ods tagsets.excelxp options=(sheet_name="FIRST SHEET");

proc print data=first;

run;

ods tagsets.excelxp options=(sheet_name="SECOND SHEET");

proc print data=second;

run;

...

ods tagsets.excelxp close;

[Aman4SAS]

I had done same thing but its creating separate excel file tmp, tmp1 , tmp2, automatically.  i m looking for single excel file with all sheets.

only diff is in ur and my code is , i m using proc report and u are using proc print

[RW9]

So you have one ods tagsets.excelxp file=, and at the end one ods tagsets.excelxp close; and it is creating different files for each proc?  That is very strange indeed, can you post your code?

[Aman4SAS]

yes , its creating separate excel sheet .

I have tried with proc print but still doing same.

ODS listing close;

ods tagsets.excelxp file="I:\anuj\temp folder\tmp.xls";

ods tagsets.excelxp options=(sheet_name="FIRST SHEET");

proc print data =  Tab7_NSA ; run;

ods tagsets.excelxp options=(sheet_name="SECOND SHEET");

proc print data = TableC_SA_all  ; run;

ods tagsets.excelxp options=(sheet_name="THIRD SHEET");

proc print data =SA_table7_sorted   ; run;

ods tagsets.excelxp close;

ODS listing;

[RW9]

This will create one file with three tabs (there shouldn't be an = after options, hence the program didn't run):

ODS listing close;
ods tagsets.excelxp file="s:\temp\rob\tmp.xls";
ods tagsets.excelxp options(sheet_name="FIRST SHEET");

proc print data =  sashelp.cars ; run;

ods tagsets.excelxp options(sheet_name="SECOND SHEET");

proc print data = sashelp.cars  ; run;

ods tagsets.excelxp options(sheet_name="THIRD SHEET");

proc print data = sashelp.cars ; run;

ods tagsets.excelxp close;
ODS listing;

[Ksharp]

ods tagsets.excelxp file="c:\temp\tmp.xls"  options(Sheet_Interval='Proc');

proc print data=sashelp.class;run;

proc print data = sashelp.cars  ; run;

ods tagsets.excelxp close;

Xia Keshan

[Aman4SAS]

Thanks Keshan,

I am having a new problem with my excel sheets,

i am creating multiple excel files with multiple sheets.

its running fine but i getting sheet names with addition number

i.e my sheet name must be xyz but in file i m getting xyz 2 , abc 3 etc

is there any option to get rid of this?

Thanks

[Ksharp]

Sorry , you can't get rid of these number . Try RW9's code .

ods tagsets.excelxp file="c:\temp\tmp.xls"  options(Sheet_Interval='Proc' Sheet_Name='xx ');

proc report data=sashelp.class nowd ;run;

proc report data = sashelp.cars nowd  ; run;

ods tagsets.excelxp close;

[Aman4SAS]

Ok Thanks,

Is there any option to replace same name's existing sheet?

[Ksharp]

Can't you replace the same name sheet with RW9's code ?

[Aman4SAS]

its adding new sheet using _1, _2 so on

[Cynthia_sas]

Hi:

  When I run RW9's code, I do *NOT* observe extra numbers getting appended to the sheet name value. Therefore, there must be SOMETHING different about your code from what you originally posted or you must have changed the code posted by RW9. Here's the code I have posted. As you can see from the screen shot, there are no extra numbers appended to the sheet names. If you run this code, as it is, and you get numbers appended to the sheet names, then I would recommend that you open a track with Tech Support.

  If you CHANGE the code, then you must show how you changed the code, because several people have responded to you that there are ways to generate multi-sheet workbooks without getting numbered sheets and except for the first code you posted, you have not posted any other code. Therefore, it is impossible for anyone to replicate your results. As you can see from my second and third screen shot, there is a way to cause numbers in the sheet names depending on the method you choose to generate multiple sheets. The second screen shot shows the code that generates the names "my stuff", "my stuff 2" and "my stuff 3" as sheet names. If you choose to use the method shown, with sheet_interval, then as you can see the normal behavior is for numbers to get appended.

  If you don't like the numbering behavior, change your code to the example that RW9 gave you. If you cannot post code and data for testing here in the forum, then perhaps you would get a quicker resolution by working with Tech Support. They can look at ALL your code and ALL your data and help you come to a definitive resolution. (Also, this is NOT a graphics question. So if you are going to continue with ODS and PROC REPORT questions, please do NOT post these questions to the Graphics forum. Post them to the ODS and Base Reporting forum.)
      

Cynthia

ODS listing close;

ods tagsets.excelxp file="c:\temp\tmp.xml" style=htmlblue;

ods tagsets.excelxp options(sheet_name="FIRST SHEET");

proc print data =  sashelp.cars(obs=5) ; run;

ods tagsets.excelxp options(sheet_name="SECOND SHEET");

proc print data = sashelp.shoes(obs=5)  ; run;

ods tagsets.excelxp options(sheet_name="THIRD SHEET");

proc print data = sashelp.prdsale(obs=5) ; run;

ods tagsets.excelxp close;

ODS listing;

[Aman4SAS]

Hi when u re-run the code it will not replace previous one , it will just new sheet with same name sufix _1_2 so on, Problem comes when u create charts with ur time series with ur existing sheet and next month when u will run this code u will need to create chart again, specialy when u need to create all MSA's sheet nearly 400, thats y i m looking solution to just replace numbers or sheet , like sas with proc export. Thanks

[RW9]

Ok, I see what you are getting at.  What you have to remember is that SAS produces outputs, it does not run the Excel system.  Therefore the output from SAS will be reproduced each time, there is not real update function.  You can trick it slightly with Excel Libnames, but its not very good.  Yes, proc export can do some basic things as well, but is built to just export a table.  SAS and Excel are very different pieces of software and Excel is really badly misused across the board.

So, if you have an Excel template and you need to work to that then you need to find ways, via Excel, to get to that.  Two options stand out - DDE - this is quite old technology that MS invented to allow cross communication between applications and Office.  It has most functionality up to about pre 2000 release of Excel and SAS supports it via DDE commands, here is an example: http://www.nesug.org/proceedings/nesug05/pos/pos15.pdf

This may however not do exactly what you want, so the other option I see for you is to export your data from SAS into a readable format, say CSV, then write a set of VBA macros in Excel (not necessarily the same workbook as your output), these macros would load the CSV data, then open your template workbook, post the data where it needs to go, and then update graphs and such like.

There is a third option, not something I would recommend.  This is to learn the Open Office structure.  This is the newer Office format, this is a ZIP file (even though the file extension is XLSX or DOCX - just rename to .ZIP), which within contains various folders and XML data.  Other than VBA code, this is how all of your data and graphs etc. are stored, hence if you know how to build this you can pretty much do anything you want, however its not straight forward (and VBA is still compiled so you wouldn't build that).

The reason is that you are trying to use a piece of software - Excel - as both a database, calculation and reporting tool.  It is not, its just a glorified calculator.  So IMO you need to either change your approach to use Excel, or change the output requirement to suit the reporting functionality of SAS.

A final note, here is an example of VBA code to be run in Excel which opens a list of files from a directory, this could be a good starting point if you wanted to write the VBA import/update macro: Spreadsheet Page Excel Tips: Getting A List Of File Names Using VBA