Date:         Sun, 9 May 2004 14:49:34 -0400
Reply-To:     Kevin Roland Viel <kviel@EMORY.EDU>
Sender:       "SAS(r) Discussion" <SAS-L@LISTSERV.UGA.EDU>
From:         Kevin Roland Viel <kviel@EMORY.EDU>
Subject:      Re: Question about DDE
In-Reply-To:  <BJEOLJAHPLOLHNCNOFBNCEGPCPAA.ma015b8234@blueyonder.co.uk>
Content-Type: TEXT/PLAIN; charset=US-ASCII

On Sun, 9 May 2004, ma015 b8234 wrote:

> Dear SAS-L Readers:
>
> I am trying to open a worksheet with X command and then write some data into
> it and then save it with date and time stamp. The code I have written does
> not seem to be working. Can anyone point me in the right direction. The code
> is given below.
>
>
> John
>
> options symbolgen mprint mlogic;
> x "I:\Reports\Monthly_Report.xls";
> FILENAME otherway DDE "EXCEL|SYSTEM";
> data _null_;
> format time1 time8.;
>   %global ddmmmyyyy;
>   %global _hour;
>   date=today();
>   time1=time()/3600;
>   time2=ceil(time1);
>   call symput('ddmmmyyyy',put(date,date9.));
>   call symput('hour',put(time2,$8.));
>   run;

No need for a data step:

%let ddmmmyyyy = %sysfunc( today(), date9 ) ;

Note the missing period.  I learned that from a Pete Crawford post.


> data _Null_;
>    file otherway;
>     put '[SELECT("R1C1")]';
>     put '[SAVE.as("I:\Reports\Monthly_Report&ddmmmyyyy&_hour.xls")]';

This macro variable will not resolve.

Try:

put
%unquote(%str(%'[SAVE.as%(%"I:\Reports\Monthly_Report)&ddmmmyyyy&_hour%str(.xls%"%)]%';));


>     put '[close()]';
>   run;
>

Ian Whitlock has written about macro quoting in a SUGI (25?) paper.  I can
confirm that on Monday.

Good luck,

Kevin

PS I am not very versed in X or SYSTEM calls, but there is an alternative
method of starting Excel.  See the Koen Vyverman papers from SUGI 27 or
the most recent SESUG (10?).




Kevin Viel
Department of Epidemiology
Rollins School of Public Health
Emory University
Atlanta, GA 30322