topic Re: using dde where path contains an apostrophe in SAS Programming

using dde where path contains an apostrophe

reef — Sun, 29 May 2016 18:45:35 GMT

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;

Re: using dde where path contains an apostrophe

ChrisNZ — Mon, 30 May 2016 03:18:23 GMT

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?

Re: using dde where path contains an apostrophe

Kurt_Bremser — Mon, 30 May 2016 04:17:53 GMT

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.

Re: using dde where path contains an apostrophe

reef — Tue, 14 Jun 2016 17:00:07 GMT

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.