Proc Export to Excel: how to retain SAS formatting

[dmorrell]

I have to produce about 60 Excel files for distribution to others so they can enter data and return to me. I've got a macro to cycle through the master SAS datasets and produce the 60 Excel files, each with 2 sheets. The master SAS dataset has formatting on many variables (comma, $, %, etc.). The problem is two-fold: 1) the formatting doesn't transfer to Excel and 2) the Excel column widths are all default width. This means I still have to touch all 60 Excel files to "pretty them up". Not quite what I had in mind.

Any suggestions?

thanks

Doug

[Reeza]

Try Tagsets.ExcelXP.

Probably need to read up a bit on it first (Recommended)

But it will allow you generate formatted XML docs that are readable by Excel.

http://support.sas.com/rnd/base/ods/odsmarkup/excelxp_demo.html

[dmorrell]

I was hesitant to go ODS, but after following the link I was able to generate output that works pretty well. If only it would output in native Excel rather than xml.... My end users don't like to learn new things.

thanks!

[Reeza]

If you save the file as .xml you don't get any warnings, but not sure what would require the end user to learn new things.

There's a macro somewhere on support.sas.com that will convert it to XLS or you could do that via DDE (which opens and resaves as different type).

I have code for the DDE but its at work

[dmorrell]

Yes, I did the .xml, but when the user modifies then saves, Excel throws a warning about how the file type does not support certain Excel functionality. This is just the type of thing I'd like to avoid because they'll think something is "wrong", regardless of front end instructions to the contrary.

I'm not familiar with DDE so if you have a chance to send an example I'd like to see it. thanks!

[ArtC]

When your user saved did they use a 'save as' and save to native .XLSX?  My experience (limited because of a lack of semi-colons) is that this avoids the message.

[Reeza]

It works interactively with Excel so you do need to have Excel on your computer and this can't be done on a server AFAIK.

I was saving to PDF which is available in 2007+ but if you want excel version you can play around with that 57 number.

Here is a link to a listing, I think you need 51 but not sure.

http://www.pcreview.co.uk/forums/fileformat-list-t3779204.html

*Open Excel;

options noxwait noxsync;

x '"C:\Program Files\Microsoft Office\Office14\excel.exe"';

*Wait for it to start up;

data _null_;

    z=sleep(3); /* wait 3 seconds for Excel to start */

run;

filename cmds dde 'excel|system';

*Open file;

data _null_; /* talk to DDE, no output data */

    file cmds;

    put '[open("C:\Temp\Sample File.xlsx")]';

    x=sleep(2); /* wait 3 seconds for it to open */

run;

*Save as PDF;

data _null_;

    file cmds;

    put '[error(false)]';

    put '[Save.as("C:\Temp\Sample Output.pdf", 57)]'; *The 57 will need to be changed to whatever works for your dept/Excel version;

run;

*Exit Excel;

data _null_;

    file cmds;

    put '[error(false)]';

    put '[quit()]';

run;

[pawan]

Morrell, I would suggest a simple way, Just create an Excel template(excel sheet with formatted fields ---- you can keep this template with no data -- just the empty fields with formats applied) manually for each of your reports and then load the respective data from your source into the excels, given with the name starting with the respective template name(excel). You migth not get your result so easily but it is not too complex. You can retain your formatted columns in the excel reports with this concept. All you need is to design a macro to pick the template and add some extra string to the name(something like current date) and then load the data into it. Believe me it is simple. All the best!!

[sniff_1]

Run a Excel macro modul to do your formats?

options noxsync noxwait ;

        filename sas2xl dde 'excel|system';

        data _null_;

        length fid rc start stop time 8;

         fid=fopen('sas2xl','s');

         if (fid le 0) then do;

          rc=system('start excel');

          start=datetime();

          stop=start+10;

          do while (fid le 0);

           fid=fopen('sas2xl','s');

           time=datetime();

           if (time ge stop) then fid=1;

          end;

         end;

        rc=fclose(fid);

        run;

        data _null_;

            file sas2xl;

            put '[error(false)]';

            PUT "[open("'"'"&OutputFile&FakBez..xlsm"'"'")]";

            put '[workbook.activate("Total")]';

            put '[Run("Start_Mod",False)]'; '

            put '[error(false)]';

            put '[Save()]';

            put '[Close()]';

        run;

        filename sas2xl clear;