Help using Base SAS procedures

SAS MACRO statements with open excel document (DDE)

Accepted Solution Solved
Reply
Frequent Contributor
Frequent Contributor
Posts: 139
Accepted Solution

SAS MACRO statements with open excel document (DDE)

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

 

 

 

 

 


Accepted Solutions
Solution
‎09-25-2015 06:23 AM
Super User
Posts: 19,782

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

[ Edited ]

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

View solution in original post


All Replies
Super User
Posts: 11,343

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

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?

Frequent Contributor
Frequent Contributor
Posts: 139

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

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

 

 

Super User
Posts: 11,343

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

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.

Frequent Contributor
Frequent Contributor
Posts: 139

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

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

Super User
Posts: 7,771

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

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.

 

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Solution
‎09-25-2015 06:23 AM
Super User
Posts: 19,782

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

[ Edited ]

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

Frequent Contributor
Frequent Contributor
Posts: 139

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

Reeza; That worked! Thank you! Lawrence
🔒 This topic is solved and locked.

Need further help from the community? Please ask a new question.

Discussion stats
  • 7 replies
  • 516 views
  • 1 like
  • 4 in conversation