BookmarkSubscribeRSS Feed
Ronein
Meteorite | Level 14

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;

 
 

 

 

5 REPLIES 5
Ksharp
Super User
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
Meteorite | Level 14

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

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
Meteorite | Level 14
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
Super User

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;

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

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
  • 5 replies
  • 630 views
  • 1 like
  • 2 in conversation