I have been using dde to send data to an excel file without a problem until I found one case where the file path contains an apostrophe. In this case the dde link fails due to the apostrophe in the path. I tried every masking function to no avail and in the end I had to remove the apostrophe from the folder name. Has anyone resolved a similar case?
Example,
data d; do x=1 to 10; y=exp(-x); output;end; run;
%let xlf = %bquote(c:\temp\sas\dde'tests\test.xlsm);
filename xl dde "exce|[&xlf.]sheet1!r1c1:r11c2" notab lrecl=200;
data _null_;
file xl;
set d;
put x y;
run;
Few people will be able to test as most have had to move on from DDE.
Your syntax works fine in SAS when DDE is not involved:
%let xlf = %bquote(c:\temp\tes't.xlsm);
filename XL "&xlf" ;
data _null_ ;
file XL ;
put 'a';
run;
So I suspect the DDE mechanism gets confused by the apostrophe. Have you tried to double the apostrophe? or to escape it?
Moving on, sadly, DDE will be less and less supported. See http://blogs.sas.com/content/sasdummy/2014/10/14/dde-is-doa/ (from 2014).
So time to rewrite your DDE logic using another (maybe less practical, but more time-proof) method?
As single (and double) quotes are used in shell scripting, they have no place in file and path names. So I'd rather tackle the problem at its source and remove the special characters than trying an unnecessary and complicated workaround. Keep in mind that those characters will cause problems elsewhere, too.
Thanks to all.
I replied as soon as answers were sent but was not aware that my final update was not recorded on the site.
I have opted to remove the apostrophe from the filename and path.
Best regards and thanks again.
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.