topic Re: ODS Excel - Cell Formatting for Entire Sheet in ODS and Base Reporting

ODS Excel - Cell Formatting for Entire Sheet

Tina1202 — Thu, 24 May 2018 20:28:21 GMT

I'm using the ODS EXCEL statement to create an Excel file. I can format the cells using all of the PROC REPORT options, but only the cells containing data are formatted on the resulting sheet. Is there a way to tell Excel to format all cells in the target sheet the same way, regardless if data is written to them from the ODS EXCEL statement? The reason is that I need to add more rows from a text file and want that data to be properly formatted.

Re: ODS Excel - Cell Formatting for Entire Sheet

Cynthia_sas — Fri, 25 May 2018 00:38:46 GMT

Hi:
How are you adding "more rows from a text file" to your PROC REPORT output? Are you doing it in a COMPUTE block with a LINE statement or are you manually copying the text in after SAS creates the ODS EXCEL output?

Can you share some of your data and your code, or else, if you can't share your date, then mock up an example program using SASHELP data (like SASHELP.CLASS, SASHELP.CLASSFIT or SASHELP.SHOES.

Your question doesn't make sense to me if you are creating output using PROC REPORT and ODS EXCEL -- SAS can only control what SAS places on the worksheet/workbook. So after the data is written, then SAS is out of the picture and it's up to you and the Excel tools for any other formatting you want to do.

Cynthia

Re: ODS Excel - Cell Formatting for Entire Sheet

Tina1202 — Wed, 30 May 2018 12:14:22 GMT

Cynthia - thanks for the response. I guess I should have given more details. I have a files that use more memory in the ODS process than my system allows, so to get around that, I'm writing 1 observation to the formatted Excel file, then writing the rest of my observations to a delimited text file, which I'm then importing into the Excel file. I was hoping to find a way to format the whole sheet when I write the one observation, but it would make sense that SAS can only control the observations it's writing. So I just have to manually update the formats of the Excel columns prior to the text import.