using excel dde in macro

cbob — Fri, 09 Mar 2012 02:48:51 GMT

Hi All,

I have this below code which works fine.

Option noxwait nosync symbolgen;

x "C:\Progra~1\Micros~2\office12\excel.exe";

Data _null_;

rc=sleep(5);

Run;

Filename command dde "excel|system";

/* yyyy_mm format */

%let yyyy_mm = %sysfunc(TODAY(),YEAR4.)_%sysfunc(TODAY(),MMDDYY2..)

/* File name is C:\Common 2012_03.xls */

%Let namexls = %Str(%'[open("C:\Common &yyyy_mm..xls")]%');

Data _null_;

File command;

put %Unquote(&namexls);

Run;

Data _null_;

File command;

put '[quit()]';

Run;

The above code opens an excel file and writes the data w/o any issues. Now, i have 3 excel files(Jan12-Mar12) to read so i thought of wrapping the above code inside a macro..

/* Extract date in yymm format */

%Let yymm = %sysfunc(putn(%sysfunc(intnx(month,%sysfunc(today()),-0,end)),yymmn4.));

%Let yymm1L = %sysfunc(putn(%sysfunc(intnx(month,%sysfunc(today()),-1,end)),yymmn4.));

%Let yymm2L = %sysfunc(putn(%sysfunc(intnx(month,%sysfunc(today()),-2,end)),yymmn4.));

Option noxwait nosync symbolgen;

%Macro loop(yymm);

x "C:\Progra~1\Micros~2\office12\excel.exe";

Data _null_;

rc=sleep(5);

Run;

Filename command dde "excel|system";

/* yyyy_mm format */

%let yyyy_mm = %sysfunc(year(%sysfunc(inputn(&yymm.,yymmn4.))))_%substr(&yymm,3.2);

%Let namexls = %Str(%'[open("C:\Common &yyyy_mm..xls")]%');

Data _null_;

File command;

put %Unquote(&namexls);

Run;

Data _null_;

File command;

put '[quit()]';

Run;

%Mend;

%loop(&yymm)

%loop(&yymm1L)

%loop(&yymm2L)

;

I don't get any error, but it says 0 records were written to file command. I guess I am doing something incorrect in the below data step

Data _null_;

File command;

%put %unquote(&namexls);

Run;

Any thoughts??

using excel dde in macro

art297 — Fri, 09 Mar 2012 03:38:53 GMT

I don't have the time to test your code, but don't you reall want a put rather than a %put?

using excel dde in macro

cbob — Fri, 09 Mar 2012 13:31:49 GMT

"put" worked..Thank you!!

topic using excel dde in macro in SAS Programming

using excel dde in macro

using excel dde in macro

using excel dde in macro