topic Re: Import correct format from an excel file in SAS Programming

Import correct format from an excel file

_Manhattan — Tue, 22 Aug 2023 12:14:46 GMT

Greetings,

I'm in the process of importing character variables from an Excel file, and I've observed that SAS doesn't carry over the formatting from Excel. For instance, text formatted as bold in Excel appears as plain text after importing. I considered the possibility of modifying the data within Excel in a manner that would prompt SAS to automatically apply formatting (like using asterisks in Excel to indicate text that should be imported as bold into SAS). However, I haven't come across any information regarding this approach. Is there a method to import the applied formatting along with the data?

Best regards

Re: Import correct format from an excel file

PaigeMiller — Tue, 22 Aug 2023 13:15:46 GMT

Do you mean you want bold in Excel to show us as bold in SAS? There is no such thing as bold text in a SAS data set.

Re: Import correct format from an excel file

_Manhattan — Tue, 22 Aug 2023 14:02:37 GMT

Well yes, that is more or less what I am talking about. As far as I know, it is possible to change the font format inside of a proc step (e. g. proc report). But as I am preparing my data in Excel I was hoping that there is an easier way than writing separate code for every single word that needs to be bold or underlined.

Kind regards

Re: Import correct format from an excel file

PaigeMiller — Tue, 22 Aug 2023 14:06:30 GMT

@_Manhattan wrote:

Well yes, that is more or less what I am talking about. As far as I know, it is possible to change the font format inside of a proc step (e. g. proc report). But as I am preparing my data in Excel I was hoping that there is an easier way than writing separate code for every single word that needs to be bold or underlined.

PROC REPORT and PROC TABULATE (and possibly a few other PROCs that I can't think of right now) allow bold output. I am not aware of a way for SAS to know that certain text or numbers it just read from Excel was bold in Excel.

Re: Import correct format from an excel file

Kurt_Bremser — Tue, 22 Aug 2023 14:18:26 GMT

A character variable in SAS is just a sequence of bytes. There is no place in the header of a dataset to store fonts or the like.

Re: Import correct format from an excel file

ballardw — Tue, 22 Aug 2023 14:20:28 GMT

@_Manhattan wrote:

Well yes, that is more or less what I am talking about. As far as I know, it is possible to change the font format inside of a proc step (e. g. proc report). But as I am preparing my data in Excel I was hoping that there is an easier way than writing separate code for every single word that needs to be bold or underlined.

Kind regards

To a certain extent all of that cell level, if not just part of cell, formatting is inappropriate for any data system that sets properties on a variable ("column") level. Microsoft SQL Server and Access data bases do not maintain such.

Without explicit examples it is hard to make appropriate suggestions but I have received files where cell level formatting, cell background color, for example contains information that belongs in another variable, such as a condition flag value to indicate that information.

Re: Import correct format from an excel file

Tom — Tue, 22 Aug 2023 14:36:35 GMT

No.

If you want to see how Excel is storing that information you could try unpacking the zipped XML files that make up an XLSX file. You could perhaps write your own method of extracting the text with whatever markup characters Excel has stored to make it possible for excel to change fonts, colors, etc.

Or you could have Excel write the data to an HTML file and then try to parse the HTML code for the text of interest and see what html codes Excel generated to mimic the text markups.

In terms of reproducing such mid string changes in text you will have to store ODS escape sequences into the text strings. Then when you print the values using ODS output (to say EXCEL or HTML of PDF files) then you might be able to recreate the appearance of the text in the EXCEL file.