ODS TAGSETS

[robertrao]

Hi Team,

I want to export two datasets into the same Excel but into different Tabs.

I cant use Proc Export because formats are not being applied.

So could anyone help me convert this code into using Tagsets so that formats can be applied

%macro export(dsname,sheet_Name);

proc export data=&dsname

outfile="C:\Da\Pr\tomy-20121011001(2)\xyz.xls"

dbms=excel replace;

sheet=&sheet_Name;

run;

%mend;

%export(dsname,sheet_Name);

%export(dsname,sheet_Name);

Thanks

[Reeza]

Why don't you give it a try and we can help with the code instead of writing it for you from scratch.

Here's some basic reference, the first one is good and you're looking for sheet_interval and sheet_name options in the second one.

http://support.sas.com/rnd/base/ods/odsmarkup/excelxp_demo.html

http://support.sas.com/rnd/base/ods/odsmarkup/excelxp_help.html

[robertrao]

Thanks for the detailed information. I am right now going to export into two different files and later in the week I will read the information you provided.

I will surely take a lot of time  to read and understand it

Thanks

[Cynthia_sas]

Hi:

And, in addition to Reeza's suggestion, my paper (http://support.sas.com/resources/papers/proceedings11/266-2011.pdf) does have code examples of using TAGATTR to control the formats that you want to send to Excel you use TAGSETS.EXCELXP.

Before you rush into a macro solution, you should have some working SAS code that produces the results you want. That (starting with working code) is considered a best practice. Even though you have a macro solution for your PROC EXPORT, using TAGSETS.EXCELXP is going to require new code, so you must test that new code before you "macroize" it.

Here's something to get you started. Now, you only need to get the TAGATTR piece working. And after you have working code, and only after that, work on "macroizing" the code.

cynthia

ods tagsets.excelxp file="c:\temp\workbook.xml"

    style=sasweb;

ods tagsets.excelxp options(sheet_name="Wombat");

proc print data=sashelp.class(obs=5) noobs;

run;

ods tagsets.excelxp options(sheet_name="Koala");

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

run;

ods tagsets.excelxp close;

[robertrao]

Hi,

Thanks for the detailed information.

I scanned through your paper and find that TAGATTR is used with proc report!!

In my case I just have two datasets and I want to export them into two seperate seperate Tabs of the same Excel sheet

The above code is fine(no need of a macro) for just 2 datasets.

Do I need to do a proc report with TAGATTR to get the result with formats applied ?????

IF i understand the above code correctly style=sasweb option with the odstagsets brings the formats applied from  the dataset(we will not have the problem of formats not being applied)???

Please correct me

Also when I work with your code and my datasets have as many as several thousands of records the output window is getting filled up before the result is seen. How to avoid thazt???

Thanks

[Reeza]

Run the example Cynthia posted.

Then change the datasets to your own and see what happens.

[robertrao]

Hi,

I ran the code which Cynthia posted and it works fine. Two tabs get created i the same XML

BUT my datasets have approx 50000 records and it keeps on printing in the output window and before I have a chance to see the file the output fills up and a message pops up

Please help me resove this

Regards

[Reeza]

Turn off ods listing before your code. And if you close the message then open the file you should be fine anyways.

ods listing close;

[robertrao]

Thank youuuuuuuuuuuu so very much

Learnt new topic today.

Regards

[robertrao]

Hi Team,

I want to mark this question as answered but dont get to see the options for that.

Pleasde help