All of our reports use ODS Tagsets (proc print or proc report) to send tables from SAS to Excel in XLS format. We are thinking to transfer them in XLSX (workboook) format because sometimes it takes forever to open/transfer large XLS files. In our experience, XLSX is quick to open and transfer. We use 'label' to give them appropriate headers in the proc print/proc report.
I tried to use 'label' for ODS Excel but it did not work. I think it's because ODS Excel does not support Tagsets??? I am not sure. For that matter, I do not think it support any Options/Styles etc. Does anyone know how to go around this so I can use 'label' in XLSX format??? Obviously, I could label each variable in proc sql before I get to ODS Excel but that would be too much work for the number of reports we have.
Below is an example of a piece of a code.
ods Excel close;
ods Excel style=Analysis file="&path./report..xlsx"
options(sheet_name='Counts'
autofilter='none'
orientation='landscape'
absolute_column_width = '20,20,15,20,23,21'
row_heights = '15,15');
proc print data=Final noobs
style(header) = [ font_style=italic font_weight = bold font_size = 10pt
just = center vjust = center foreground = black background = #CCCCFF]
style(column) = [ font_size = 9pt];
label
clientname='Client Name'
clientnumber = 'Client Name'
ID = 'ID'
Count1='Count1'
Count2='Count2'
percent='Percent';
run;
ods Excel close;
If by Tagsets you mean Tagsets.Excelxp then part of the performance is the conversion from XML, which is what the Tagset generates and then opening it with Excel.
Proc Print expects the option Label on the Proc statement to display variable labels instead of just the name. You don't have that set. Try
proc print data=Final noobs label style(header) = [ font_style=italic font_weight = bold font_size = 10pt just = center vjust = center foreground = black background = #CCCCFF] style(column) = [ font_size = 9pt]; label clientname='Client Name' clientnumber = 'Client Name' ID = 'ID' Count1='Count1' Count2='Count2' percent='Percent'; run;
I tried to use 'label' for ODS Excel but it did not work.
This is not enough information for us to understand what happened. Please be specific. If there are warnings or errors in the log, SHOW US the entire log (we need to see 100% of the log for this code; do not pick and choose parts to show us, and not show us other parts). If the output is wrong, SHOW US the incorrect output and explain what is wrong and what you would like to see.
If by Tagsets you mean Tagsets.Excelxp then part of the performance is the conversion from XML, which is what the Tagset generates and then opening it with Excel.
Proc Print expects the option Label on the Proc statement to display variable labels instead of just the name. You don't have that set. Try
proc print data=Final noobs label style(header) = [ font_style=italic font_weight = bold font_size = 10pt just = center vjust = center foreground = black background = #CCCCFF] style(column) = [ font_size = 9pt]; label clientname='Client Name' clientnumber = 'Client Name' ID = 'ID' Count1='Count1' Count2='Count2' percent='Percent'; run;
proc print data=Final noobs
style(header) = [ font_style=italic font_weight = bold font_size = 10pt
just = center vjust = center foreground = black background = #CCCCFF]
style(column) = [ font_size = 9pt];
You didn't specify the LABEL option on the PROC PRINT statement, so by default it uses the variable names not labels.
PROC PRINT uses the name of the variable as the column heading in the following two circumstances:
@SASMom2 wrote:
All of our reports use ODS Tagsets (proc print or proc report) to send tables from SAS to Excel in XLS format. We are thinking to transfer them in XLSX (workboook) format because sometimes it takes forever to open/transfer large XLS files. In our experience, XLSX is quick to open and transfer. We use 'label' to give them appropriate headers in the proc print/proc report.
I tried to use 'label' for ODS Excel but it did not work. I think it's because ODS Excel does not support Tagsets??? I am not sure. For that matter, I do not think it support any Options/Styles etc. Does anyone know how to go around this so I can use 'label' in XLSX format??? Obviously, I could label each variable in proc sql before I get to ODS Excel but that would be too much work for the number of reports we have.
Below is an example of a piece of a code.
ods Excel close;
ods Excel style=Analysis file="&path./report..xlsx"
options(sheet_name='Counts'autofilter='none'
orientation='landscape'
absolute_column_width = '20,20,15,20,23,21'
row_heights = '15,15');
proc print data=Final noobs
style(header) = [ font_style=italic font_weight = bold font_size = 10pt
just = center vjust = center foreground = black background = #CCCCFF]
style(column) = [ font_size = 9pt];label
clientname='Client Name'
clientnumber = 'Client Name'
ID = 'ID'
Count1='Count1'
Count2='Count2'
percent='Percent';
run;ods Excel close;
Proc Print is about the only proc that requires the Label statement.
If other procedures aren't displaying labels you may want to run this bit of code:
proc options option=label;run;
If the LOG shows something like this:
NOLABEL Disables procedures from using labels with variables.
Then someone has set the system option to NOLABEL. Which should restore desired behavior of showing labels for other procedures by using
Options label;
Depending on your environment you may have intended to be temporary option setting persist from session to session.
Available on demand!
Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.
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.