DATA Step, Macro, Functions and more

using excel dde in macro

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 8
Accepted Solution

using excel dde in macro

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



Accepted Solutions
Solution
‎03-08-2012 10:38 PM
PROC Star
Posts: 7,467

using excel dde in macro

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

View solution in original post


All Replies
Solution
‎03-08-2012 10:38 PM
PROC Star
Posts: 7,467

using excel dde in macro

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

Occasional Contributor
Posts: 8

using excel dde in macro

"put" worked..Thank you!!

🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 2 replies
  • 180 views
  • 0 likes
  • 2 in conversation