DATA Step, Macro, Functions and more

ODS TAGSETS

Reply
Super Contributor
Posts: 1,040

ODS TAGSETS


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

Super User
Posts: 17,749

Re: ODS TAGSETS

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

Super Contributor
Posts: 1,040

Re: ODS TAGSETS

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

SAS Super FREQ
Posts: 8,740

Re: ODS TAGSETS

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;

Super Contributor
Posts: 1,040

Re: ODS TAGSETS

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

Super User
Posts: 17,749

Re: ODS TAGSETS

Run the example Cynthia posted.

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

Super Contributor
Posts: 1,040

Re: ODS TAGSETS

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

Super User
Posts: 17,749

Re: ODS TAGSETS

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;

Super Contributor
Posts: 1,040

Re: ODS TAGSETS

Thank youuuuuuuuuuuu so very much

Learnt new topic today.

Regards

Super Contributor
Posts: 1,040

Re: ODS TAGSETS

Hi Team,

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

Pleasde help

Ask a Question
Discussion stats
  • 9 replies
  • 321 views
  • 5 likes
  • 3 in conversation