Questions about SAS DDE,
in [SAS]
|
Prev: copy external file
Next: Identity_Insert and SAS/Access
From: "Choate, Paul on 29 Nov 2006 13:05 Glad it helped Xamil - First of all, I think Alan's comments are worth a look. If you want to stick with this DDE approach then a couple suggestions: Add a couple macro variables indicating the first run or a subsequent run. Use the flag to conditionally comment out sections in your SAS code to create Macro1 on the first run. Hiding it is a good idea. Use the flags to hide and unhide the macro sheet. I think this code does what you want. Note I also delete sheet1-sheet3 on the first run. <sas code> %let shtname=A; %let first=*; %let notfirst=; options noxsync noxwait xmin; 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; ¬first data _null_; ¬first file sas2xl; ¬first put '[workbook.insert(3)]'; ¬first put '[workbook.delete("sheet1")]'; ¬first put '[workbook.delete("sheet2")]'; ¬first put '[workbook.delete("sheet3")]'; ¬first run; data _null_; file sas2xl; put '[workbook.insert(1)]'; put '[workbook.move(,"junk.xls",1)]'; &first put '[workbook.unhide("macro1")]'; put '[workbook.move("macro1","junk.xls",1)]'; put '[error(false)]'; run; filename xlmacro dde 'excel|macro1!r1c1:r100c1' notab lrecl=200; data _null_; file xlmacro; put "=workbook.name(,%bquote("&shtname"))"; put '=halt(true)'; put '!dde_flush'; run; data _null_; file sas2xl; put '[workbook.next()]'; put '[run("macro1!r1c1")]'; put '[error(false)]'; put '[workbook.hide("macro1")]'; run; </sas code> In subsequent runs use: %let shtname=B; %let first=; %let notfirst=*; %let shtname=C; %let first=; %let notfirst=*; Etc. If you are in SAS9 you can save yourself this entire headache by using the libname Excel engine. Take a look at this paper if you are so inclined. :) http://www2.sas.com/proceedings/sugi31/024-31.pdf regards Paul Choate DDS Data Extraction (916) 654-2160 -----Original Message----- From: xamil [mailto:xaamil(a)yahoo.com] Sent: Tuesday, November 28, 2006 5:51 PM To: Choate, Paul(a)DDS; sas-l(a)listserv.uga.edu Subject: Re: Questions about SAS DDE, inserting and renaming more than one sheet Paul, Thanks again for your help! I have followed your guidance and codes, and it successfully renames the new sheet inserted at each run (iteration), but still with some problem: ('Fronpage' is the only sheet in JUNK.xls before I run the program.) I ran the program 3 times,with new value for the %let statement at each run, and here are what happened: In the 1st run (iteration), With %let=Alpha, the workbook displays Macro1, Alpha, Sheet1, Frontpage. In the 2nd run, with %let=Beta, the workbook displays Macro1 Beta sheet4 sheet3 Alpha Sheet1 Frontpage . In the 3rd run, with %let=Gamma, the workbook displays Macro1 Gamma Sheet6 Sheet5 Betta Sheet4 sheet3 Alpha Sheet1 Frontpage. Is it possible to prevent those sheet#n and macro1 from the display? I have tried to use this code to hide the Macro1 from the sight data _null_; file sas2xl; put '[error(false)]'; put '[Workbook.hide("macro1")]'; run; it worked for the first run and second run, but not for the 3rd run. Don't really know why ? By the way Here is what I want to accomplish: an automation process with GUI implementation in Excel template for those who dont use SAS. The clients have the choice to fix a value for one or more parameters and click 'RUN button' from the 'Fronpage' sheet,and SAS run the program in the backgroud and dump the result to a new sheet at each run. Clients wants to compare the current result with the result from previous iteration. So Clients can run the program as many times as they want. /*** Here is the code I have tried**/ options noxsync noxwait xmin; 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; %let shtname=Alpha; data _null_; file sas2xl; put '[workbook.insert(1)]'; put '[workbook.move(,"junk.xls",1)]'; run; data _null_; file sas2xl; put '[workbook.insert(1)]'; put '[workbook.move(,"junk.xls",1)]'; put '[workbook.insert(3)]'; put '[workbook.move("macro1","junk.xls",1)]'; run; filename xlmacro dde 'excel|macro1!r1c1:r100c1' notab lrecl=200; data _null_; file xlmacro; put "=workbook.name(,%bquote("&shtname"))"; put '=halt(true)'; put '!dde_flush'; run; data _null_; file sas2xl; put '[workbook.next()]'; put '[run("macro1!r1c1")]'; put '[error(false)]'; run; /* data _null_; file sas2xl; put '[error(false)]'; put '[Workbook.hide("macro1")]'; run; */ --- "Choate, Paul(a)DDS" <pchoate(a)DDS.CA.GOV> wrote: > Xamil - > > The problem is that Excel names macro and worksheets > sequentially - macro1, macro2, macro3, etc. and > sheet1, sheet2, sheet3, etc. On the second > iteration your program refers to incorrect macro and > sheet names. > > Not quite sure what all you are trying to > accomplish, but here's a couple hints. First you > don't need to delete the macro1 sheet. Just reuse > it until the job is finished and then delete it. > > As for sequential numbering of sheets, when you > insert a sheet, it is active, so you don't need to > refer to it by default. > > If you have sheet1-3 already, then this inserts > "sheet4" and moves it to position #1. > > put '[workbook.insert(1)]'; > > put '[workbook.move(,"my_workbook.xls",1)]'; > > This way you don't need to worry about the > sequential numbering > > > Use this and relative position to create and rename > sheets... assuming Macro1 already exists, then this > code creates a new sheet (by default in the first > position), moves Macro1 in front of it, and renames > the new sheet using a relative reference. > > data _null_; > > file sas2xl; > > put '[workbook.insert(1)]'; > > put '[workbook.move(,"my_workbook.xls",1)]'; > > /* put '[workbook.insert(3)]';*/ > > put '[workbook.move("macro1","my_workbook.xls",1)]'; > > run; > > filename xlmacro dde 'excel|macro1!r1c1:r100c1' > notab lrecl=200; > > data _null_; > > file xlmacro; > > put "=workbook.name(,%bquote("&shtname"))"; > > put '=halt(true)'; > > put '!dde_flush'; > > run; > > data _null_; > > file sas2xl; > > put '[workbook.next()]'; > > put '[run("macro1!r1c1")]'; > > put '[error(false)]'; > > run; > > Thus you can create as many new sheets as you want > and rename them in the same job. > > > hth > > Paul Choate > (916) 654-2160 > DDS Data Extraction > > ________________________________ > > From: SAS(r) Discussion on behalf of xamil > Sent: Sun 11/26/2006 1:07 PM > To: SAS-L(a)LISTSERV.UGA.EDU > Subject: Questions about SAS DDE , inserting and > renaming more than one sheet > > > > Dear all SAS users: > I am using DDE to transfer SAS output to Excel. One > of > the task is to insert more than one worksheet one at > a > time to the existing(currently running) excel > workbook, and then rename the worksheet according to > user's specified name. I dont want to save and > reopen > the workbook every time when i need to insert a new > worksheet and then renaming it. > Inserting and renaming the first sheet as REPORT1 > works fine, but when i try to insert the 2nd > worksheet > and rename it to REPORT2. I will give error message > and won't rename the new worksheet REPORT2. Does > anyone have any idea how to solve this problem ? > > Many thanks in advance !! > > Here is my code: > Assuming the workbook MY_WORKBOOK is running: > > /*******************************************/ > %let shtname=Report1; > > options noxsync noxwait xmin; > 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 '[workbook.next()]'; > put '[workbook.insert(1)]'; > put '[workbook.move("sheet1","my_workbook.xls",1)]'; > run; > > data _null_; > file sas2xl; > put '[workbook.next()]'; > put '[workbook.insert(3)]'; > put '[workbook.move("macro1","my_workbook.xls",1)]'; > run; > > filename xlmacro dde 'excel|macro1!r1c1:r100c1' > notab > lrecl=200; > > data _null_; > file xlmacro; > put "=workbook.name(%bquote("sheet1"), > %bquote("&shtname"))"; > put '=halt(true)'; > put '!dde_flush'; > file sas2xl; > put '[run("macro1!r1c1")]'; > put '[error(false)]'; > run; > > data _null_; > file sas2xl; > put '[workbook.delete("Macro1")]'; > run; > > > > > > ________________________________________________________________________ ____________ > Cheap talk? > Check out Yahoo! Messenger's low PC-to-Phone call > rates. > http://voice.yahoo.com > ________________________________________________________________________ ____________ Want to start your own business? Learn how on Yahoo! Small Business. http://smallbusiness.yahoo.com/r-index |