11-05-2012 06:02 PM
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.
11-05-2012 06:17 PM
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.
11-07-2012 07:28 PM
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.
11-07-2012 08:51 PM
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
11-07-2012 08:58 PM
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!
11-07-2012 11:00 PM
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.
11-08-2012 10:43 AM
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.
options noxwait noxsync;
x '"C:\Program Files\Microsoft Office\Office14\excel.exe"';
*Wait for it to start up;
z=sleep(3); /* wait 3 seconds for Excel to start */
filename cmds dde 'excel|system';
data _null_; /* talk to DDE, no output data */
put '[open("C:\Temp\Sample File.xlsx")]';
x=sleep(2); /* wait 3 seconds for it to open */
*Save as PDF;
put '[Save.as("C:\Temp\Sample Output.pdf", 57)]'; *The 57 will need to be changed to whatever works for your dept/Excel version;
12-03-2012 06:22 AM
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!!
11-09-2012 02:09 AM
Run a Excel macro modul to do your formats?
options noxsync noxwait ;
filename sas2xl dde 'excel|system';
length fid rc start stop time 8;
if (fid le 0) then do;
do while (fid le 0);
if (time ge stop) then fid=1;
put '[Run("Start_Mod",False)]'; '
filename sas2xl clear;