SAS: Proc Export to Excel with layout?

Question

My first post here with a question. Hope I do this "in the right way". I've build a query in SAS (mainly PROC SQL) which is generating a monthly report, based on certain criteria.

I'm exporting the tables I create to an Excel-file using Proc Export. This is the line of code I use:

proc export data=work.par_reg
  outfile="&myfilerfp." 
  dbms=xlsx
  replace;
  sheet="Particulieren regulier";
run;

I have more tabs in this file but the Excel that's being created, is only text / numbers without any lay-out. Is it possible to either apply some kind of lay-out to this Excel (like white background, yellow titles, etc) or is it possible to export into an existing template sheet which already has a lay-out?

Thanks for the help!

Did something along those lines years ago, but used SAS to produce a flat csv file which was then used as the data input to a template file with lots of charts and analysis. So, perhaps you could re-think your output route, but I understand if it is not possible. — Solar Mike
What version of sas? Look into sas ODS, Output Delivery System. You can write HTML, xml, and native xlsx, all with formatting, using PROC REPORT or other reporting procs. PROC EXPORT is about exporting data, but no pretty formatting. — Quentin
I'm using SAS BI 9.4. I'm faily new to using SAS to be honest so any more info on ODS you have will be very helpful! I will start looking for it myself as well :) Thanks! — user7677227
Start with Tagsets.ExcelXP - it has more options but generates an XML file that can be opened in Excel. ODS Excel will create a native Excel file. I would recommend this paper as index and the papers listed within have many examples and code — Reeza

Joe Joe · Accepted Answer · 2017-03-08T16:35:18

PROC EXPORT is only intended for what you see - getting values onto the sheet.

If you want styling, then you have a few choices, but your best option is ODS EXCEL, available in SAS 9.4 TS1M1 or later. You can see some tips on using it from Chris Hemedinger here or read the documentation here.

The simple usage though is like this:

ods excel file="c:\blah\whatever\myfile.xlsx" style=[your style];

proc print data=your_dataset;
run;

ods excel close;

You can use PROC TEMPLATE to define the style options (colors, backgrounds, fonts, whatnot) or create a style using CSS and use cssstyle= if you know CSS.

SAS: Proc Export to Excel with layout?

1 Answers