If you have SAS/Access for PC file formats, you can open and save Excel files directly within SAS, manipulate, and save them just as you would a SAS data set. With the right ODS tagsets, you can get fancy on the output side with such things as formatting and populating multi-sheet workbooks. See David Kelley's paper at:

http://www.nebsug.org/Abstracts2006.htm#Creating%20Multi-Sheet%20Microsoft%2 0Excel

HTH,

Rob

Rob Rohrbough Omaha, NE, USA (Chair, NE SAS Users Group)

> -----Original Message----- > From: Tree Frog [mailto:tree.frog2@HOTMAIL.COM] > Sent: Tuesday, August 21, 2007 9:10 PM > Subject: Re: Interacting with external applications from within SAS > > On Aug 21, 11:13 pm, nos...@HOWLES.COM ("Howard Schreier <hs AT dc-sug > DOT org>") wrote: > > On Tue, 21 Aug 2007 03:07:26 -0700, Tree Frog <tree.fr...@HOTMAIL.COM> > wrote: > > >Hi all > > > > >Is DDE still the preferred (or only?) way to manipulate application- > > >specific files such as excel spreadsheets from a SAS session? When I > > >try running something along the lines of the code in David Brown's > > >paper about his sas2xl macro... [see > > > > http://www2.sas.com/proceedings/sugi30/092-30.pdf] > > > > > > > > > > > > > > > > >options xsync noxwait xmin; > > >filename sas2xl dde 'excel|system'; > > >/* this opens xl if not already open */ > > >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 "[OPEN(""&path&file"")]"; > > >put '[error(false)]'; > > >put "[SAVE.AS(""&path&file"", 1, , FALSE, , FALSE)]"; > > >put '[File.Close(TRUE)]';run; > > >run; > > > > >...I'm getting messages about insufficient authorisation for DDE > > >filenames, and about shell escape not being valid in this SAS > > >session. Gotta admit to being almost entirely at sea on these > > >matters. I'm running EG4.1, local 9.1.3, on WinXP. [would it be > > >different if I was running the code in non-interactive/batch mode?] > > > > >What I want to do is to open an Excel SpreadsheetML file and save it > > >as a native .xls, preferably in 95/97 format, from within SAS. I'd > > >like to be able to do that without having to open an Excel session if > > >possible. > > > > Then DDE is not what you want. It manipulates the app (Excel), not the > file > > directly. > > > > > > > > > > > > >Where should I be aiming my google search? > > > > >Thanks > > > > >Tree Frog- Hide quoted text - > > > > - Show quoted text -- Hide quoted text - > > > > - Show quoted text - > > Any ideas on what I _do_ want then? > Am I restricted to opening an instance of Excel and using DDE? > > Thanks > > TF