BookmarkSubscribeRSS Feed
robertrao
Quartz | Level 8


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

9 REPLIES 9
Reeza
Super User

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
Quartz | Level 8

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
SAS Super FREQ

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
Quartz | Level 8

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
Super User

Run the example Cynthia posted.

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

robertrao
Quartz | Level 8

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
Super User

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
Quartz | Level 8

Thank youuuuuuuuuuuu so very much

Learnt new topic today.

Regards

robertrao
Quartz | Level 8

Hi Team,

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

Pleasde help

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 9 replies
  • 1119 views
  • 5 likes
  • 3 in conversation