BookmarkSubscribeRSS Feed
reef
Calcite | Level 5

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;

3 REPLIES 3
ChrisNZ
Tourmaline | Level 20

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?

Kurt_Bremser
Super User

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.

reef
Calcite | Level 5

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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 3 replies
  • 1040 views
  • 1 like
  • 3 in conversation