DDE - INSERT VARIABLES INTO EXCEL AS MACRO VARIABLE

[AlexeyS]

Hello. I want to insert into excel a few variables(suppose three variables). I create a macro variable that represent list of variables.

What's wrong?

proc sql noprint;
select name into : vars separated by '09'x
from dictionary.columns
where LIBNAME = upcase("work")
and MEMNAME = upcase("report") 
and name like '%happy';
quit;

filename excelw dde "EXCEL|Sheet1!R2C5:R100C7" notab;
data _null_;
file excelw;
set report;
put "&vars"n;
run;

[RW9]

What does the log say?  As a suggestion I would move away from using technology that is 30 years out of service (DDE)m and which doesn't work at all in several scenarios.  If you want data in Excel, then use ods excel or tagsets.excelxp or one of those.  Much more control and modern.  If you need something else, explain the need.  DDE I wouldn't even bother trying to debug it.

[SuryaKiran]

Try this:

proc sql noprint;
select name into : vars separated by " "
from dictionary.columns
where LIBNAME = upcase("work")
and MEMNAME = upcase("report") 
and name like '%happy';
quit;

filename excelw dde "EXCEL|Sheet1!R2C5:R100C7" notab;
data _null_;
file excelw;
set report;
put &vars;
run;

[ballardw]

---

@AlexeyS wrote:

Hello. I want to insert into excel a few variables(suppose three variables). I create a macro variable that represent list of variables.

What's wrong?

proc sql noprint;
select name into : vars separated by '09'x
from dictionary.columns
where LIBNAME = upcase("work")
and MEMNAME = upcase("report") 
and name like '%happy';
quit;

filename excelw dde "EXCEL|Sheet1!R2C5:R100C7" notab;
data _null_;
file excelw;
set report;
put "&vars"n;
run;

---

Without a clear example of input or output it is very hard to answer what might be "wrong".

We cannot tell:

1) If the &vars macro variable was successfully created

2) What the possible value of &varsmay be

Also, the Put "&vars"n will not work if there are more than 1 variable name in the value of &vars as the Put statement would be expecting a single variable name that exists in the data set Report. Assuming &vars was build from SASHELP.class you could be creating a statement equivalent to:

put "Name Sex Age Height Weight"n;

and would be looking for a single variable with that name.

Tab delimited list is going to be right out for a single put. You would need to parse the list to get each separate variable. 

Be aware that there is other software out there that interferes with DDE. If these applications are running then DDE will not work at all. The example I am very aware of is Cisco Jabber. The process has to be KILLED using task manager, not just closing any active window, before any DDE commands will be accepted.