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??
I don't have the time to test your code, but don't you reall want a put rather than a %put?
I don't have the time to test your code, but don't you reall want a put rather than a %put?
"put" worked..Thank you!!
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.