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
screenshot 2
screenshot 3
screenshot 4
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
screenshot 6
screenshot 7
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
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 🐵
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.
@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
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 🐵
thanks. It is working perfectly well here!
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.
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.