BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
Chang
Quartz | Level 8

hi all,

 

I would like to export mutliple SAS data sets to an excel workbook. Each worksheet should contain the SAS data,  a title and a subtitle appeared as I have specified. I am confused by the discrepancy between the title appeared in the result window and the title appeared in the exported excel, and the discrepancy between the subtitle seen in the result window and the subtitle seen in the exported excel. There is no error or warning in my SAS log. To make it easy to understand this post, I have created 3 summary tables and inserted screenshots. My full code has been attached at the end of this post.

 

setting of titles and subtitles in my macro

--------------------------------------------------------------------------------------------------------

%let title_text_2= %str('Table 2. A long title for table 2') ;

%let subtitle_text_2=%str('data based on primary procedures only');

%let title_text_3= %str('Table 3. Another long title for table 3') ;
%let subtitle_text_3=%str('data based on primary or secondary procedures');

---------------------------------------------------------------------------------------------------------

 

titles and subtitles appear in the result window        problem

--------------------------------------------------------------------------------------------------------------------------

Table 2. A long title for table 2                                  title text and position both correct   (see screenshot 1)

data based on primary procedures only                   subtitle text shown at footnote location of the table created from the previous call of the same macro (see screenshot 2)

 

Table 3. Another long title for table 3                        title text and position both correct (see screenshot 3) 

data based on primary or secondary procedures     same problem- subtitle appeared at the bottom of previou table (see screenshot 4)

-------------------------------------------------------------------------------------------------------------------------- 

 

screenshot 1

result window_title of table 2.jpg

 

 

screenshot 2

result window_subtitle of table 2.jpg

 

screenshot 3

result_window_title of table 3.jpg

 

screenshot 4

result window_subtitle of table 3.jpg

 

titles and subtitles appear in exported excel                problem

--------------------------------------------------------------------------------------------------------------------------

Table 3. This title should appear in table 3                   This is the old setting from previous run of the macro (see screenshot 5)

data based on primary procedures only                       text and position both correct (see screenshot 5)

Table 3. Another long title for table 3                            text and position both correct (see screenshot 6)

(nothing here)                                                                subtitle printed at the bottom of table 2 (see screenshot 7)

-------------------------------------------------------------------------------------------------------------------------- 

 

screenshot 5

exported excel_title and subtitle of table 2.jpg

 

screenshot 6

exported excel_title of table 3.jpg

 

screenshot 7

exported excel_subtitle of table 3.jpg

 

 

Here my full code:

%sysmacdelete export_excel_workbook_title_b ;
%macro export_excel_workbook_title_b;

/*----------------------------define specification for the workbook-------------------------------------*/
	%let filename= NIS_rectal_cancer_tables.xlsx	;

/*----------------------------define specification for worksheet 2----------------------------*/
	%let inputData_2= %str(data_pro.rectal_table02_want_a);
	%let title_text_2= %str('Table 2. A long title for table 2') ;	
	%let subtitle_text_2=%str('data based on primary procedures only');
	%let sheet_name_2= %str("table2")	;
	%let export_var_list= %str(Procedural_category Characteristics year2004-year2012 Overall 
					AAPC_CL)	;
/*----------------------------define specification for worksheet 3----------------------------*/
	%let inputData_3= %str(data_pro.rectal_table03_want_a);
	%let title_text_3= %str('Table 3. Another long title for table 3') ;
	%let subtitle_text_3=%str('data based on primary or secondary procedures');
	%let sheet_name_3= %str("table3")	;
	
/*----------------------------define output destination----------------------------------------*/
	ods excel	file="&dir_project.\&output_folder.\&filename."	;

/*----------------------------export worksheet 2----------------------------------------*/
	ods excel	options(start_at="B3" tab_color="red" 
								embedded_titles="yes"
								  sheet_interval="none"  /* for multiple procs/sheet */
								  sheet_name= &sheet_name_2. /* name the sheet tab */
								); 
		ods text=&subtitle_text_2. ;
		proc print data= &inputData_2.;
			title justify=l	&title_text_2.; /*left-align the title*/
			var &export_var_list.	 / STYLE={TAGATTR='format:text'};
		run;
/*----------------------------export worksheet 3----------------------------------------*/
		ods excel	options(start_at="B3" tab_color="red" 
									embedded_titles="yes"
							  		sheet_interval="none" 
								  	sheet_name= &sheet_name_3.
								); 
		ods text=&subtitle_text_3. 	;
		proc print data= &inputData_3.;
			title justify=l	&title_text_3.	; /*left-align the title*/
			var &export_var_list.	 / STYLE={TAGATTR='format:text'};
		run;
/*----------------------------close ODS----------------------------------------*/
	ods excel close; 
%mend;

%export_excel_workbook_title_b;

Any idea? I hope the same ODS style can be kept. That is one of the few that look acceptable.

 

Cheers,

Chang 

1 ACCEPTED SOLUTION

Accepted Solutions
RW9
Diamond | Level 26 RW9
Diamond | Level 26

Yes, I think you are hitting a confrontations between the detsination (Excel) and ods here.  Remember Excel destination is new and not totally finished.  ODS text applies before an outpu and after an output depending on where it is.  When that was released, the excel destination did not exist, with its multi sheet:

http://support.sas.com/documentation/cdl/en/odsug/61723/HTML/default/viewer.htm#a003134393.htm

 

My first question would be, why do you want to do this with ods text anyways?  A simple title structure would suffice:

ods excel file="s:\temp\rob\multisheet_excel.xlsx";
ods excel options(start_at="B3" tab_color="red" embedded_titles="yes" sheet_interval="none" sheet_name= "class");
title1 j=l h=12pt "Table : student data";
title3 j=l h=8pt "19 observations and 5 variables";
proc print data=sashelp.class;
  var Name Sex Age Height Weight / style={tagattr='format:text'};
run;

ods excel options(start_at="B3" tab_color="red" sheet_name= "electric");
title1 j=l h=12pt "Table : Electric power revenue";
title3 j=l h=8pt "48 observations and 3 variables";
proc print data=sashelp.electric;
  var Customer year Revenue / style={tagattr='format:text'};
run;
ods _all_ close;

 

A couple of tips for your coding also - use spaces rather than tabs, only put in relevant comments (export a table for instance doesn't tell me anything), and use consistent capitalisation - caps sounds like shouting 🐵

View solution in original post

4 REPLIES 4
RW9
Diamond | Level 26 RW9
Diamond | Level 26

Hi,

 

Too long a post for me to read all of it.  I would question a) why is it all in a macro, with all the titles and things as macro variables, as nothing there needs any sort of macro part at all.  Simplfy your code, just show a small example of code using a sashelp table that generates an example, so we can run it.  As for your issue, well firstly I would suggest not using Excel, as it is not a good review tool.  Secondly titles are generally only seen in the print prview section of Excel.  From what I can guess something simple like:

ods excel ...

title1 ...

title2 ...

proc report...

title1...

title2...

proc report...

ods excel close

Should suffice.

Chang
Quartz | Level 8

@RW9 wrote:

Hi,

 

Too long a post for me to read all of it.  I would question a) why is it all in a macro, with all the titles and things as macro variables, as nothing there needs any sort of macro part at all.  Simplfy your code, just show a small example of code using a sashelp table that generates an example, so we can run it.  As for your issue, well firstly I would suggest not using Excel, as it is not a good review tool.  Secondly titles are generally only seen in the print prview section of Excel.  From what I can guess something simple like:

ods excel ...

title1 ...

title2 ...

proc report...

title1...

title2...

proc report...

ods excel close

Should suffice.


hi RW9,

 

To answer your first question, the code is working well when a SAS data, a title and a subtitle are exported to a single excel file. The macro in my post was modified from the macro that does this exportation. I don't know why the title and the subtitle are not shown properly in a multisheet excel workbook. 

 

To simplify my code, I have tried to export sashelp.class and sashelp.electric to an excel workbook. Same problems with the title and the subtitle in the exported file. Please advise on an alternative. It would be great if each worksheet can have its own title and subtitle as I specify. My SAS file and the exported excel have been attached as a zip file. 

 

/*get sample data from SASHLEP*/
data class; set sashelp.class; run; /*19 observations and 5 variables*/
data electric;set sashelp.electric; run; /*48 observations and 16 variables*/

/*export data to an excel workbook*/
ods excel	file="C:\SAS_lessons\multisheet_excel.xlsx"	;

/*----------------------------export a table----------------------------------------*/
	ods excel	options(	start_at="B3" 
									tab_color="red" 
									embedded_titles="yes"
								  	sheet_interval="none"  /* for multiple procs/sheet */
								  	sheet_name= "class" /* name the sheet tab */
									); 
		ods text=  "19 observations and 5 variables" ;
		proc print data= class;
			title justify=l "Table : student data"; /*This title is not seen in the excel*/
			var Name Sex Age Height Weight	 / STYLE={TAGATTR='format:text'};
		run;
/*----------------------------export another table----------------------------------------*/
		ods excel	options(start_at="B3" tab_color="red" 
									embedded_titles="yes"
							  		sheet_interval="none" 
								  	sheet_name= "electric"
								); 
		ods text="48 observations and 3 variables"	;
		proc print data= electric;
			title justify=l	"Table : Electric power revenue"	; 
			var Customer year Revenue	 / STYLE={TAGATTR='format:text'};
		run;

	ods excel close; 

Thanks

RW9
Diamond | Level 26 RW9
Diamond | Level 26

Yes, I think you are hitting a confrontations between the detsination (Excel) and ods here.  Remember Excel destination is new and not totally finished.  ODS text applies before an outpu and after an output depending on where it is.  When that was released, the excel destination did not exist, with its multi sheet:

http://support.sas.com/documentation/cdl/en/odsug/61723/HTML/default/viewer.htm#a003134393.htm

 

My first question would be, why do you want to do this with ods text anyways?  A simple title structure would suffice:

ods excel file="s:\temp\rob\multisheet_excel.xlsx";
ods excel options(start_at="B3" tab_color="red" embedded_titles="yes" sheet_interval="none" sheet_name= "class");
title1 j=l h=12pt "Table : student data";
title3 j=l h=8pt "19 observations and 5 variables";
proc print data=sashelp.class;
  var Name Sex Age Height Weight / style={tagattr='format:text'};
run;

ods excel options(start_at="B3" tab_color="red" sheet_name= "electric");
title1 j=l h=12pt "Table : Electric power revenue";
title3 j=l h=8pt "48 observations and 3 variables";
proc print data=sashelp.electric;
  var Customer year Revenue / style={tagattr='format:text'};
run;
ods _all_ close;

 

A couple of tips for your coding also - use spaces rather than tabs, only put in relevant comments (export a table for instance doesn't tell me anything), and use consistent capitalisation - caps sounds like shouting 🐵

Chang
Quartz | Level 8

thanks. It is working perfectly well here!

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

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
  • 4 replies
  • 4225 views
  • 0 likes
  • 2 in conversation