SAS MACRO statements with open excel document (DDE)

[LB]

Hello all-

I have a small script as such

filename ddecmd dde 'excel|system';

data _null_;
file  ddecmd lrecl=500;;
put '[OPEN("T:\P_PPR\PPR_Program\xxx\PPR\2015MY\Report\ROS\2015 MidYear ROS Med Ctr All Outliers.xls")]';

put '[SELECT("R10")]';
DO I = 1 TO 50;
put '[INSERT(2)]';
END;
put '[Save()]';

put '[CLOSE("T:\P_PPR\PPR_Program\xxx\PPR\2015MY\Report\ROS\2015 MidYear ROS Med Ctr All Outliers.xls")]';

run;

It works fine as is-

The issue I am having is attempting to embed this into a macro var as so that

put '[OPEN("T:\P_PPR\PPR_Program\xxx\PPR\2015MY\Report\&MEDCTR\2015 MidYear &MEDCTR Med Ctr All Outliers.xls")]';

becomes

put '[OPEN("T:\P_PPR\PPR_Program\xxx\PPR\2015MY\Report\&MEDCTR\2015 MidYear &MEDCTR Med Ctr All Outliers.xls")]';

The former analyst had this syntax:

put %unquote(%str(%'[open("T:\P_PPR\PPR_Program\QOS\PPR\2015MY\Report\&MEDCTR.\2015 MidYear &MEDCTR. Med Ctr All Outliers.xls")]'%));

but it does not seem to work.

I have tried a couple of other ways to no avail-

Any assistance greatly appreciated.

Lawrence

[ballardw]

It helps to describe how the code doesn't work. Does it generated errors, if so please describe them.

Does the speadsheet exist before you open it? Does the folder exist?

[LB]

Ballard-

To your questions- Both the file and the folder exists already.  In addition, excel is already open as well (through an X command)

As to the errors-

As is without the macro input the script works fine. If you attempt to use the macro language as stated in the thread  then nothing happens-

I have tried a couple of other iterations that have resulted in the file not being found as the Macro vars remain undefined.  

Thanks for your help

Lawrence

[ballardw]

I find that when I have issues similar to that making a single macro variable that contains the entire string is helpful. First I can debug the value before actually adding the complication of what ever is trying to use it. Also where multiple layers of quotes get involved I can find a single place to look for macro quoting issues.

[LB]

I realized there was an error in description:

It works fine as is-

The issue I am having is attempting to embed this into a macro var as so that

put '[OPEN("T:\P_PPR\PPR_Program\xxx\PPR\2015MY\Report\&MEDCTR\2015 MidYear &MEDCTR Med Ctr All Outliers.xls")]';

properly is rendered as:

put '[OPEN("T:\P_PPR\PPR_Program\xxx\PPR\2015MY\Report\ROS\2015 MidYear ROS Med Ctr All Outliers.xls")]';

And opens the file

Thank you

[Kurt_Bremser]

Try to switch the use of quotes. When you want macro variables to be substituted by their content in a string, the outermost quotes should be double quotes. Inside the string you can then use single quotes without problem.

[Reeza · Accepted solution]

Try this, it worked for me

put "[OPEN(""T:\P_PPR\PPR_Program\xxx\PPR\2015MY\Report\&MEDCTR\2015 MidYear &MEDCTR Med Ctr All Outliers.xls"")]";

Replace outer single quote ['] with double quote["]

Replace inner double quote ["] with [""]

[LB]

Reeza; That worked! Thank you! Lawrence