topic Re: export to multiple sheets in ODS and Base Reporting

export to multiple sheets

Ronein — Tue, 10 Oct 2017 07:11:51 GMT

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;

Re: export to multiple sheets

Kurt_Bremser — Tue, 10 Oct 2017 07:40:27 GMT

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

Re: export to multiple sheets

Ronein — Tue, 10 Oct 2017 08:41:21 GMT

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

Re: export to multiple sheets

Ronein — Tue, 10 Oct 2017 08:51:41 GMT

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;

Re: export to multiple sheets

Kurt_Bremser — Tue, 10 Oct 2017 10:57:51 GMT

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

Re: export to multiple sheets

Ronein — Wed, 11 Oct 2017 08:48:32 GMT

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?

Re: export to multiple sheets

Kurt_Bremser — Wed, 11 Oct 2017 10:48:29 GMT

Its this post: https://communities.sas.com/t5/ODS-and-Base-Reporting/export-to-excel-with-special-needs/m-p/402615#M19558