Proc Export +Proc Report to Fancy Excel Output

SAS day 55

Background Story:

Once we need to generate an Adverse Event listing for the medical team to review in excel, I was amazed by my colleague’s colorful output from SAS. I was just simply happy to look at the output!

Example:

I will use SAS.cars to generate the output

SAS Code:

*-----------------------------*;
*   To generate excel file*;
*-----------------------------*;
ods _all_ close;
ods escapechar = '@';

ods escapechar='~';
options topmargin = 0.5 in bottommargin = 0.5 in leftmargin = 0.5 in rightmargin = 0.5 in;

ods excel file="/happy/car.xlsx";

ods excel options(embedded_titles='yes' embedded_footnotes='yes' orientation="landscape"
    frozen_headers='yes'
    pages_fitwidth='1'
    pages_fitheight='1'
    print_header_margin='0'
    print_footer_margin='0'
/*      center_horizontal='yes'*/
/*      center_vertical='yes' */
/*      absolute_column_width='18'*/
    autofilter='all' );

ods excel options(sheet_name='Fancy Cars');

title "Car dataset ";
proc report data=car nowd split="$" style(header) = {background = $altclr. font_size= 10pt vjust=top};
*format model $4.; /*(avoid truncate the 0s in front)*/
   column make model type msrp ;
   define  make /   "Car brand" style(column)=[tagattr="format:00000000000000" cellwidth=3.5cm] style(header)={background=#f9f3e8};
   define  model/   "Car Model" style(column)=[cellwidth=3.5cm] style(header)={background=#f9f3e8};
   define  type /   "Car Type" style(column)=[cellwidth=4.5cm] style(header)={background=#f9f3e8};
   define  msrp /   "Price /$In dollars " style(column)=[cellwidth=3.5cm] style(header)={background=#f9f3e8};
run;
quit;
  
ods excel close;
ods listing;

It was really generous that my colleague Yajnes willing to share his fancy Proc Export +Proc Report for Excel output code with me! I think the best way to express my gratitude to him is to share the knowledge with more people! 

Although it is quarantine time, I wish we can make something colorful for ourselves, like the Proc Export Excel!

Happy Studying!