EZ Study

Actuarial Biology Chemistry Economics Calculators Confucius Engineer
Physics
C.S.

SAS programming Skills
SAS Tutorial: How to format Excel file by SAS DDE

Question: As statistician, we often got the "by-request" to automate the output, for example, automate the process to generate some beautiful excel file with specific format. How can we accomplish this?
Answer: This tutorial we will cover the following step in formatting an excel file:
        • Open and close (save) the spreadsheet
        • Setup the page orientation, centering, fitting, margins
        • Apply different column widths to the data
        • Control font size and style of titles, heading, body
        • Apply custom data fonts
        • Align data center, left, right

        /***************************************************/
        options noxwait noxsync;
        x '"C:\Program Files\Microsoft Office\Office14\EXCEL.EXE"';
        /* use office14 for office 2010, use office12 for office 2007. */
        data _null_; rc = sleep(3); run;
        /* put sas to sleep for 3 seconds to call excel */
Click to Download SAS/Excel Tutorial paper   Excel 4.0 Macro Functions Help: Macrofun.hlp

        filename ddecmds DDE "excel|system";
        data _null_; file ddecmds;
        put "[open(""directory\excel_template.xlsx"", 0, true)]";
        run; /* To open excel template */

        data _null_; rc = sleep(3); run;
        proc sql; select count(*) into: max
        from datain; quit; /*get the num of records */

        %let maxCount=%eval(&max+11);
        filename input dde
        "Excel|directory\[excel_template.xlsx]all!R2C1:R&maxCount.C100" notab;

        /* To select/filter cell satisfying different creteria */
        data _null_; file ddecmds;
        put '[workbook.activate("[excel_template.xlsx]sheet1")]';
        put "[select(""R1C1:R&maxCount.C5"")]";
        put '[filter(3,"=Some criteria")]';
        put '[patterns(1,,4,true)]';
        /* To highlight cells by green(colorid=4), list of color & id */
        put "[filter(1,""=name1"", 2, ""=name2"")]";
        put '[font.properties("Verdana","Bold",16,,,,,,,3)]';
        /* Hightlight the cell with font size=16 and red color(colorid=3) */

        put '[FILTER.SHOW.ALL()]'; /* Html color codes */

        /* To insert a new column */
        data _null_; file DDEcmds;
        put '[select("C4")]';
        put '[insert(4)]';run; /* INSERT a column from the right of column 4*/

        /* format the cell in particular numerical format */
        data _null_; file DDEcmds;
        put     '[Format.Number("#0.0")]';
                  '[Format.Number("$#,##0.00")]'
                  '[Format.Number("$#,##0")]'
                  '[Format.Number("0.00%")]'
                  '[Format.Number("0%")]'
                  '[Format.Number("#,##0")]' ); run;

        /* To cancel the last command */
        put '[undo()]'; run;

        /* Insert a Heading for the New Column 4 */
        filename CELL1 DDE "EXCEL|excel_name!R1C4";
        data _null_; file CELL1;
        put 'New Header Name'; run;

        /* To format the column width and cell alignment */
        data _null_; file ddecmds;     put '[select("C1:C4")]';
        put '[column.width(,,,3)]'; /* 3=Autofit */
        put '[alignment(3,true,3,0)]'; /* 2=Left,3=Center,4=Right */

        data _null_; file DDEcmds;     put '[select("R2C4")]';
        /* 2 more left column divided by 1 one right column */
        put '[formula.fill("=RC[+2]/RC[-1]","R2C4:R20C4")]'; run;

        /* Replace Nulls with N/A */
        put '[formula.replace("","N/A",1,,false,false)]';

        /* To save a excel template as a new excel file */
        data _null_; file DDEcmds;
        put '[error(false)]'; /*prevent prompting a window for replace confirmation */
        put '[save.as ("directory\excel_FileName.xls")]'; run;


Related links:
Continue to: How to create excel pivot table in SAS   SAS tutorial home
Back to: How to generate dummy variables automatically?   Statistics tutorial home