BookmarkSubscribeRSS Feed
JSML
Calcite | Level 5

Hi,

 

I have a SAS program that creates a table of values (through multiple data and proc steps), and that ends with a Proc Export to export that table to one of the tabs of an Excel template (xls).  

 

PROC EXPORT DATA = work.abc OUTFILE= “mypath to Excel template\xyz_template.xls"

    DBMS=EXCEL REPLACE;

     SHEET="table1";

RUN;

 

There is no pre-formatting in the 'table1' sheet of the template - only the column headings and some data that gets replaced when the proc export is run; every cell is in "General" format and aligned to center. After the export, some cells in the table are bottom aligned, and some (random ones) are center bottom aligned - and the strangest thing is that the latter cells are coloured (have a light blue background colour) and a black border around them).  There is no specified colour or alignment style in my SAS program, so I don't understand where the formatting is coming from.

 

I have tried deleting that sheet in the template altogether, as proc export creates a new one if it does not find one to replace, and this time the same type of formatting happened when proc export created the 'table1' sheet, except that the center-bottom-aligned cells now got coloured in a different colour (purple).

 

I've attached a sample of how my Excel output looks like.

 

Wondered if someone else encountered this issue.

 

Thanks!

2 REPLIES 2
Reeza
Super User

You say xls but included an XLSX file, was that a typo?

 

Check if you have any conditional formats in your template

JSML
Calcite | Level 5

Hi, sorry, no the Excel template is xls.  I included a sample xlsx file because I am writing this post from home and the SAS program and excel template I mentioned are at work (at the office), so no access to the actual xls file at the moment (but the formatting looks exactly as in the sample file I attached).

 

I did check earlier today if there was conditional formatting in the sheet and there was none...

sas-innovate-wordmark-2025-midnight.png

Register Today!

Join us for SAS Innovate 2025, our biggest and most exciting global event of the year, in Orlando, FL, from May 6-9. Sign up by March 14 for just $795.


Register now!

How to connect to databases in SAS Viya

Need to connect to databases in SAS Viya? SAS’ David Ghan shows you two methods – via SAS/ACCESS LIBNAME and SAS Data Connector SASLIBS – in this video.

Find more tutorials on the SAS Users YouTube channel.

Discussion stats
  • 2 replies
  • 1677 views
  • 0 likes
  • 2 in conversation