topic Re: Export to XLSX in SAS Programming

Export to XLSX

Ronein — Thu, 23 Sep 2021 09:47:48 GMT

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;

Re: Export to XLSX

Ksharp — Thu, 23 Sep 2021 12:12:37 GMT

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 .

Re: Export to XLSX

Ronein — Thu, 23 Sep 2021 13:28:46 GMT

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;

Re: Export to XLSX

Ksharp — Thu, 23 Sep 2021 13:38:37 GMT

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;

Re: Export to XLSX

Ronein — Thu, 23 Sep 2021 18:54:28 GMT

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;

Re: Export to XLSX

Ksharp — Fri, 24 Sep 2021 11:44:54 GMT

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;