BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
LB
Quartz | Level 8 LB
Quartz | Level 8

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

 

 

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Reeza
Super User

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

7 REPLIES 7
ballardw
Super User

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
Quartz | Level 8 LB
Quartz | Level 8

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
Super User

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
Quartz | Level 8 LB
Quartz | Level 8

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
Super User

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
Super User

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
Quartz | Level 8 LB
Quartz | Level 8
Reeza; That worked! Thank you! Lawrence

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!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

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
  • 7 replies
  • 2294 views
  • 1 like
  • 4 in conversation