topic Re: SAS MACRO statements with open excel document (DDE) in SAS Procedures

SAS MACRO statements with open excel document (DDE)

LB — Wed, 09 Sep 2015 17:50:03 GMT

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

Re: SAS MACRO statements with open excel document (DDE)

ballardw — Wed, 09 Sep 2015 20:29:52 GMT

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?

Re: SAS MACRO statements with open excel document (DDE)

LB — Wed, 09 Sep 2015 20:58:20 GMT

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

Re: SAS MACRO statements with open excel document (DDE)

LB — Wed, 09 Sep 2015 21:01:35 GMT

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

Re: SAS MACRO statements with open excel document (DDE)

Kurt_Bremser — Thu, 10 Sep 2015 05:26:04 GMT

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.

Re: SAS MACRO statements with open excel document (DDE)

ballardw — Thu, 10 Sep 2015 15:11:23 GMT

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.

Re: SAS MACRO statements with open excel document (DDE)

Reeza — Thu, 10 Sep 2015 16:53:58 GMT

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 [""]

Re: SAS MACRO statements with open excel document (DDE)

LB — Thu, 10 Sep 2015 22:15:12 GMT

Reeza; That worked! Thank you! Lawrence