The SAS Output Delivery System and reporting techniques

ODS Excel - Cell Formatting for Entire Sheet

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 8
Accepted Solution

ODS Excel - Cell Formatting for Entire Sheet

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.


Accepted Solutions
Solution
‎05-30-2018 01:04 PM
SAS Super FREQ
Posts: 9,426

Re: ODS Excel - Cell Formatting for Entire Sheet

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

View solution in original post


All Replies
Solution
‎05-30-2018 01:04 PM
SAS Super FREQ
Posts: 9,426

Re: ODS Excel - Cell Formatting for Entire Sheet

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

Occasional Contributor
Posts: 8

Re: ODS Excel - Cell Formatting for Entire Sheet

Posted in reply to Cynthia_sas

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.

 

☑ This topic is solved.

Need further help from the community? Please ask a new question.

Discussion stats
  • 2 replies
  • 306 views
  • 1 like
  • 2 in conversation