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!!
Build your skills. Make connections. Enjoy creative freedom. Maybe change the world. Registration is now open through August 30th. Visit the SAS Hackathon homepage.
Register today!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.
Select SAS Training centers are offering in-person courses. View upcoming courses for: