DATA Step, Macro, Functions and more

Retain Excel data format

Occasional Learner TL8
Occasional Learner
Posts: 1

Retain Excel data format

When using Proc Import to read an Excel file into SAS, the data doesn't seem to retain the format for a text, such as font, font size, bold, italic, etc. Wonder if there is a way to get over it, since the text field we're handling has complex format and it's hard to maninuplate in SAS.

Super User
Posts: 23,727

Re: Retain Excel data format

You're bringing data into SAS and you want to bring the bold/italic/font size over as well?


I don't think that's doable, at least not easily, but what are you calling complex and what would you expect to see? Post a sample Excel file and what you would expect to see in SAS.


It sounds like you might be storing data in your formats/fonts which is a bad way to store data. 

Super User
Posts: 10,258

Re: Retain Excel data format

DATA is DATA. SAS has the capability of assigning formats for display, but that stays within the realm of pure text, so no fonts, color, bold/italic etc.

You can get colors and other atttributes in reports with PROC REPORT and advanced use of the ODS.

Maxims of Maximally Efficient SAS Programmers
How to convert datasets to data steps
How to post code
Trusted Advisor
Posts: 1,252

Re: Retain Excel data format

Posted in reply to KurtBremser

Maybe the intention is to replicate the styles found in the Excel sheet when writing the processed data with SAS to a suitable ODS destination.


More than 10 years ago I managed to read the text color from Excel sheets via DDE, but that was very complicated (to figure out). I could imagine that, depending on the file format (especially xlsx), more recent techniques would facilitate access to Excel's formatting information, but I'm not sure. PROC IMPORT is definitely inappropriate for this task.

Ask a Question
Discussion stats
  • 3 replies
  • 4 in conversation