topic Re: Retain Excel data format in SAS Programming

Retain Excel data format

TL8 — Fri, 26 Feb 2016 19:54:59 GMT

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.

Re: Retain Excel data format

Reeza — Fri, 26 Feb 2016 21:32:32 GMT

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.

Re: Retain Excel data format

Kurt_Bremser — Fri, 26 Feb 2016 21:37:37 GMT

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.

Re: Retain Excel data format

FreelanceReinh — Fri, 26 Feb 2016 22:08:39 GMT

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.