Excel Multi-Tab Creation in UNIX-SAS

[DPraba79]

Hi All,

I have a situation like, i have to create a excel book with 2 sheet on it (Multi Tab) in unix. Example: I have a two data sets say, data set A & Dataset B. I have to put these datasets in to two sheets (sheet1 & sheet2) in a excel work book by using Unix-SAS .

Thanks in Advance!!!

[Cynthia_sas]

Hi:
You have 2 basic choices for creating multi-tab/multi-sheet workbooks:
1) PROC EXPORT (need SAS/Access to PC File Formats) or
2) ODS TAGSETS.EXCELXP

Both of these topics have appeared in previous forum postings. Since you posted in the ODS forum, however, I assume that you are interested in an ODS approach. Some starter code is shown below. That will get you started. There are many, many postings about ODS TAGSETS.EXCELXP in the forum and many user-group papers that you can find by searching on Google.

After the MULTI_SHEET.XML file has been created, you will want to FTP it from Unix to Windows so you can open the file with Excel. The file contains Excel 2003 Spreadsheet Markup Language XML and can only be opened with Excel 2003 or higher.

cynthia
[pre]
ods tagsets.excelxp file='multi_sheet.xml';

proc print data=sashelp.class;
run;

proc print data=sashelp.shoes(obs=50);
var region product sales;
run;

ods _all_ close;
[/pre]

[DPraba79]

Thanks Cythia... But when try this method.. I got only one tabl(sheet) comes out with below code (two prins)
data data1;
a=10;
run;
data data2;
b=10;
run;


ods tagsets.excelxp file='/home/testsas/tst.xml';


proc print data=data1;

proc print data=data2;

ods _all_ close; Message was edited by: DPraba79

[Cynthia_sas]

Hi:

1) Are there any LOG messages, such as
[pre]
WARNING: Tagset TAGSETS.EXCELXP not found; the default tagset
will be used instead.
[/pre]

If you see this message, what version of SAS are you running?

2) In my code (and anytime you use ODS), the RUN; statements make a difference. ODS _ALL_ CLOSE is not a step boundary. So the last PROC PRINT would not execute until AFTER the ODS file was closed. Compare my code to your code, as far as RUN statements. I have a RUN at the end of EVERY PROC PRINT step and BEFORE ODS _ALL_ CLOSE;

If you run my code, EXACTLY as I have coded it (which you should be able to do because the code uses SASHELP files), what are the results??

cynthia

[Ksharp]

Just as Cynthia said that your UNIX-SAS has not this tagsets.excelxp.
You can use another approach that Cynthia mentioned before.

[pre]
proc export data=sashelp.shoes outfile="c:\temp\op.xls" dbms=xls replace;
sheet='one';
run;

proc export data=sashelp.class outfile="c:\temp\op.xls" dbms=xls replace;
sheet='two';
run;
[/pre]



Ksharp