topic Re: Excel sheets through ODS in Graphics Programming

Excel sheets through ODS

Aman4SAS — Fri, 25 Jul 2014 12:21:29 GMT

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

Re: Excel sheets through ODS

RW9 — Fri, 25 Jul 2014 12:31:35 GMT

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;

Re: Excel sheets through ODS

Aman4SAS — Fri, 25 Jul 2014 12:43:58 GMT

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

Re: Excel sheets through ODS

RW9 — Fri, 25 Jul 2014 13:11:26 GMT

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?

Re: Excel sheets through ODS

Aman4SAS — Fri, 25 Jul 2014 13:15:50 GMT

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;

Re: Excel sheets through ODS

RW9 — Fri, 25 Jul 2014 13:23:02 GMT

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;

Re: Excel sheets through ODS

Ksharp — Fri, 25 Jul 2014 13:41:46 GMT

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

Re: Excel sheets through ODS

Aman4SAS — Mon, 04 Aug 2014 09:12:25 GMT

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

Re: Excel sheets through ODS

Ksharp — Mon, 04 Aug 2014 12:08:17 GMT

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;

Re: Excel sheets through ODS

Aman4SAS — Mon, 04 Aug 2014 12:28:39 GMT

Ok Thanks,

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

Re: Excel sheets through ODS

Ksharp — Mon, 04 Aug 2014 12:40:15 GMT

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

Re: Excel sheets through ODS

Aman4SAS — Mon, 04 Aug 2014 13:31:25 GMT

its adding new sheet using _1, _2 so on

Re: Excel sheets through ODS

Cynthia_sas — Mon, 04 Aug 2014 16:35:13 GMT

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;

Re: Excel sheets through ODS

Aman4SAS — Mon, 04 Aug 2014 19:52:09 GMT

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

Re: Excel sheets through ODS

RW9 — Tue, 05 Aug 2014 08:02:41 GMT

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

Re: Excel sheets through ODS

Aman4SAS — Tue, 05 Aug 2014 08:49:18 GMT

Thanks for your great guidance.

We are already working on ur suggested procedure. but in this case task become multi talk and very time consuming.

ODS is very helpful. but i m just missing my chart requirement with time series. i am trying to use VBA to create chart with created time series through ODS.

Thanks again.

If possible just please guide me to increase the height of line in proc report , that will help me to add my summary along with my report.

Re: Excel sheets through ODS

Ksharp — Tue, 05 Aug 2014 12:19:21 GMT

Can you delete that file before run the ods code ?

x 'delete c:\temp\tmp.xls' ;

ods tagsets.excelxp file="c:\temp\tmp.xls"

about increasing the number of column ,try the following option to make more room in a page .

option topmargin=2 leftmargin=2

Xia Keshan

Re: Excel sheets through ODS

Aman4SAS — Tue, 05 Aug 2014 12:53:14 GMT

Thanks, But i m looking for incresing height of line, becaz my lines are long and its been wrap and overlap by next line. {In case of line font size also not working, if i will remove"(*ESC*)S" and i will issue to make first line bold}

My code is below:

proc report data = TableA nowd headline headskip split= "*"

style(report)=[cellspacing=2 borderwidth=1 bordercolor=blue]

style(header)=[color=black

fontsize =3 textalign=l fontfamily = 'Times New Roman' fontweight=bold]

style(column)=[color=black

fontfamily='Times New Roman' fontsize=2 just=center]

style(lines)=[color=Black backgroundcolor=grey

fontstyle=italic fontweight=bold fontsize=1 just =l]

style(summary)=[color=cx3e3d73 backgroundcolor=cxaeadd9

fontfamily=helvetica fontsize=3 textalign=r];

column ("Heading line" (Area Totalsale Mom sdrates));

DEFINE AREA / " * * LOCATION" style(Header)=[just=center cellwidth=2 in] style(column)=[just=l];

define totalsale/ "Total ads~{super 1}*(Thousands)*&currmon" style(Header)=[just=center cellwidth=1 in] style(column)=[tagattr='format:#,##0.0'];

define mom/ "M-O-M*Change*&currmon - &prevmon" style(Header)=[just=center cellwidth=1.2 in] style(column)=[tagattr='format:#,##0.0'];

Define sdrates/"Supply/*Demand Rates~{super 2}*&msta" style(Header)=[just=center cellwidth=1.4 in] style(column)=[tagattr='format:##.00'];

compute Area;

if Area in ("NORTHEAST","SOUTH","MIDWEST","WEST") and _break_=' ' then

call define(_row_, "style",

"style=[backgroundcolor=yellow

fontfamily='Times New Roman'

fontweight=bold]");

if Area = "United States" and _break_=' ' then

call define(_row_, "style",

"style=[backgroundcolor=cxaeadd9

fontfamily='Times New Roman'

fontweight=bold]");

endcomp;

compute after ;

line "(*ESC*)S={ textalign= left Font_weight=bold font_size=1 fontfamily='times new roman'}line 1 (*ESC*)S={}";

line "(*ESC*)S={ font_size=1 fontfamily='times new roman'}line 2 (*ESC*)S={}";

line "(*ESC*)S={ font_size=1 fontfamily='times new roman'}Line 3 (*ESC*)S={}";

endcomp;

FORMAT totalads 7.1 MOM 7.1 sdrates 7.2;

run;

Re: Excel sheets through ODS

Ksharp — Tue, 05 Aug 2014 13:02:42 GMT

cellheight= ?

Re: Excel sheets through ODS

Aman4SAS — Tue, 05 Aug 2014 13:59:15 GMT

cellheight , row_height , all i have tried but not working. at the same time fontsize also not working for lines