Export to XLSX

[Ronein]

Hello,

I want to export from SAS to Excel  in the following way:

One sheet for Males (source data sashelp.class)

One sheet for Females (source data sashelp.class)

One sheet for Acura Cars ( (Source data sashelp.cars)

One sheet for Sedan and Sports cars with separate  table  (Source data sashelp.cars)

May anyone show how to do it please?

Please note that for sheet male  and sheet female I have used sheet_interval='BYGROUP' option.

The problem is how to add other elements of export 

thanks

proc sort data=sashelp.class out=class;
by sex;
run;
ods excel file= "/path/ReportX.xlsx" 
options(sheet_interval='BYGROUP' sheet_name= '#byval1');
/***Each Sex has a different sheet**/
proc print data=class;
by sex;
run;
 
/***Export to another sheet called 'Acura_Cars'***/
Title 'Acura Cars';
proc print data=sashelp.cars noobs;
where make='Acura';
Run;


/***Export to another sheet called 'Sports_Sedan cars'***/
/***with separate table for Sports and Sedan ***/
Title 'Sports/Sedan cars';
proc print data=sashelp.cars noobs;
where Type in('Sports','Sedan');
Run;
ods excel close;

 
 

[Ksharp]

proc sort data=sashelp.class out=class;
by sex;
run;


options nobyline;
ods excel file= "c:\temp\ReportX.xlsx" 
options( sheet_name= '#byval1');
/***Each Sex has a different sheet**/
proc print data=class;
by sex;
run;
 
ods excel options(sheet_name='Acura Cars');
/***Export to another sheet called 'Acura_Cars'***/
Title 'Acura Cars';
proc print data=sashelp.cars noobs;
where make='Acura';
Run;


ods excel options(sheet_name='Sports/Sedan  Cars');
/***Export to another sheet called 'Sports_Sedan cars'***/
/***with separate table for Sports and Sedan ***/
Title 'Sports/Sedan cars';
proc print data=sashelp.cars noobs;
where Type in('Sports','Sedan');
Run;
ods excel close;

Or try PROC EXPORT .

[Ronein]

Thanks,

You wrote :

I want to have 2 seprate tables in sheet Sports_Sedan_cars.

In the code that you wrote ,where do you ask to create 2 seprate tables (in same sheet Sports_Sedan_cars)?

ods excel options(sheet_name='Sports/Sedan  Cars');
/***Export to another sheet called 'Sports_Sedan cars'***/
/***with separate table for Sports and Sedan ***/
Title 'Sports/Sedan cars';
proc print data=sashelp.cars noobs;
where Type in('Sports','Sedan');
Run;
ods excel close;

[Ksharp]

OK. If I understand what you mean .

proc sort data=sashelp.class out=class;
by sex;
run;


options nobyline;
ods excel file= "c:\temp\ReportX.xlsx" 
options( sheet_name= '#byval1');
/***Each Sex has a different sheet**/
proc print data=class;
by sex;
run;
 
ods excel options(sheet_name='Acura Cars');
/***Export to another sheet called 'Acura_Cars'***/
Title 'Acura Cars';
proc print data=sashelp.cars noobs;
where make='Acura';
Run;


ods excel options(sheet_name='Sports/Sedan  Cars' sheet_interval='none');
/***Export to another sheet called 'Sports_Sedan cars'***/
/***with separate table for Sports and Sedan ***/
Title 'Sports/Sedan cars';
proc print data=sashelp.cars noobs;
where Type in('Sports');
Run;
proc print data=sashelp.cars noobs;
where Type in('Sedan');
Run;
ods excel close;

[Ronein]

Thanks again.
I am looking at the last part of your code and I want to ask if it will.work to add statement BY TYPE in order to get 2 separate tables ( instead of writing 2 proc print statements as you wrote)?

ods excel options(sheet_name='Sports/Sedan Cars' sheet_interval='none');
Title 'Sports/Sedan cars';
proc print data=sashelp.cars noobs;
where Type in('Sports','Sedan');
BY TYPE;
Run;
ods excel close;

[Ksharp]

OK. Just add one more PROC SORT.

proc sort data=sashelp.cars(where=(Type in('Sports' 'Sedan'))) out=temp;by type;run;
ods excel options(sheet_name='Sports/Sedan  Cars' sheet_interval='none');
/***Export to another sheet called 'Sports_Sedan cars'***/
/***with separate table for Sports and Sedan ***/
Title 'Sports/Sedan cars';
proc print data=temp noobs;
by type ;
Run;