topic Re: DDE - INSERT VARIABLES INTO EXCEL AS MACRO VARIABLE in New SAS User

DDE - INSERT VARIABLES INTO EXCEL AS MACRO VARIABLE

AlexeyS — Wed, 17 Oct 2018 12:13:44 GMT

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;

Re: DDE - INSERT VARIABLES INTO EXCEL AS MACRO VARIABLE

RW9 — Wed, 17 Oct 2018 12:17:02 GMT

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.

Re: DDE - INSERT VARIABLES INTO EXCEL AS MACRO VARIABLE

SuryaKiran — Wed, 17 Oct 2018 14:39:51 GMT

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;

Re: DDE - INSERT VARIABLES INTO EXCEL AS MACRO VARIABLE

ballardw — Wed, 17 Oct 2018 14:54:19 GMT

@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.