The SAS Output Delivery System and reporting techniques

export to multiple sheets

Reply
Frequent Contributor
Posts: 91

export to multiple sheets

Hello

This code export 5 tables to one excel sheet and put them as a panel (3 side by side and then 2 side by side).

I want to adjust the code to export 3 tables to "sheet1"  that will be side by side and 2 tables to "sheet2" that will be side by side

 

 


/*Create panel of tables in one excel file and one sheet*/

ods tagsets.msoffice2k_x
file="/usr/local/SAS/MidulOld/UserDir/Ron.xls"
options(panelcols="3")
style=normal;
proc print data=sashelp.prdsale(obs=3);
var actual predict country region;
where country="CANADA";
title "Canada Sales";
run;
proc print data=sashelp.prdsale(obs=3);
var actual predict country region;
where country="U.S.A.";
title "USA Sales";
run;
proc print data=sashelp.prdsale(obs=3);
var actual predict country region;
where country="GERMANY";
title "Germany Sales";
run;

ods tagsets.msoffice2k_x
options(panelcols="2") ;
proc print data=sashelp.prdsale(obs=3);
where region="EAST";
title "East Sales";
run;
proc print data=sashelp.prdsale(obs=3);
where region="WEST";
title "West Sales";
run;

ods tagsets.msoffice2k_x close;

Super User
Posts: 9,549

Re: export to multiple sheets

[ Edited ]

You can either have panelcols in HTML with tagsets.msoffice2k_x, or sheet_name in XML with tagsets.excelxp.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Frequent Contributor
Posts: 91

Re: export to multiple sheets

Posted in reply to KurtBremser

Can you please send a code and we will see together that it works.

I tried to do what you said but it didn't work

 

Frequent Contributor
Posts: 91

Re: export to multiple sheets

Posted in reply to KurtBremser

I run this code in order to get to sheets (sheet1, sheet2).

But I got for each table a seperate sheet!!

 

 

 

 

ods tagsets.excelxp file="/usr/local/SAS/MidulOld/UserDir/Ron.xls"

/*Sheet1*/
options(sheet_name='Sheet1' panelcols="3") ;
proc print data=sashelp.prdsale(obs=3)noobs;
var actual predict country region;
where country="CANADA";
title "Canada Sales";
run;
proc print data=sashelp.prdsale(obs=3)noobs;
var actual predict country region;
where country="U.S.A.";
title "USA Sales";
run;
proc print data=sashelp.prdsale(obs=3)noobs;
var actual predict country region;
where country="GERMANY";
title "Germany Sales";
run;


/*Sheet2*/
ods tagsets.excelxp options(sheet_name='Sheet2' panelcols="2");
proc print data=sashelp.prdsale (obs=3)noobs ;
where region="EAST";
title "East Sales";
run;
proc print data=sashelp.prdsale (obs=3)noobs;
where region="WEST";
title "West Sales";
run;
ods tagsets.excelxp close;

Super User
Posts: 9,549

Re: export to multiple sheets

panelcols does not work in tagsets.excelxp. Use my code provided in the other thread to create a side-by-side dataset.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Frequent Contributor
Posts: 91

Re: export to multiple sheets

Posted in reply to KurtBremser

Where can i find your code?I don't understand you

you said "Use my code provided in the other thread to create a side-by-side dataset."

Where is it please?

Super User
Posts: 9,549

Re: export to multiple sheets

Ask a Question
Discussion stats
  • 6 replies
  • 241 views
  • 0 likes
  • 2 in conversation