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??
