ODS Excel

[SASMom2]

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;

[PaigeMiller]

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.

[SASMom2]

I will send you the log and output but to reply to your question about the error, it does not give any error but when I look in to the excel, the fonts are not what I asked it to use, column headings has original variable names. It did not use the 'label'.

[ballardw · Accepted solution]

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;

[Reeza]

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.

https://documentation.sas.com/doc/en/pgmsascdc/9.4_3.5/proc/n17dcq1elcvpvkn1pkecj41cva6j.htm#n0enjwa...

PROC PRINT uses the name of the variable as the column heading in the following two circumstances:

1. if you omit the LABEL option in the PROC PRINT statement, even if the PROC PRINT step contains a LABEL statement
2. if a variable does not have a label

---

@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;

---

[SASMom2]

Thank you all for your reply. I added the word 'label' which worked. I have to try it with some other reports to make sure it works for them because even with the 'label' after NOOBS, it was not working previously.
Thank again for your prompt replies.

[ballardw]

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.