BookmarkSubscribeRSS Feed
dmorrell
Calcite | Level 5

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

8 REPLIES 8
Reeza
Super User

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
Calcite | Level 5

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
Super User

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 Smiley Sad

dmorrell
Calcite | Level 5

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
Rhodochrosite | Level 12

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
Super User

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
Obsidian | Level 7

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
Calcite | Level 5

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;

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 8 replies
  • 15813 views
  • 0 likes
  • 5 in conversation