DATA Step, Macro, Functions and more

Help re-writing macro

Accepted Solution Solved
Reply
Super Contributor
Posts: 418
Accepted Solution

Help re-writing macro

Hello everyone. I have the following Macro (attached at the bottom) which works in most cases, however a user presented a problem to it that I don't know how to solve.   Basically I have a user who has a file located in a file path that has a single quote in it.  The file path looks like follows.  C:\company\brandon's files\.

So when they try to run the macro they enter.

%Mapping_Names("C:\company\brandon's files\MultiTabs.xlsm","MultiTabs.xlsm!MultiTabsToCSV.MultiTabsToCSV")

This macro basically just runs A specific excel macro, and it works for all macros not found in a directory that contains a single quote.   I know the following section is the problem..

DATA _NULL_;

     FILE xcel;

     put  %unquote(%str(%'[open(&excelvariable)]%'));

     put  %unquote(%str(%'[run(&macrovariable)]%'));

     PUT "[quit()]";

RUN;



My "solution"

The only way I know how to fix this problem is to have the user enter two quotes in any of their calls to this macro (if their directory has a single quote in it)...  So their call would be...

%Mapping_Names("C:\company\brandon''s files\MultiTabs.xlsm","MultiTabs.xlsm!MultiTabsToCSV.MultiTabsToCSV")

Obviously I could have the macro just scan through these variables and do a tranwrd to make the single '  into double '   so the user could enter whatever they want... HOWEVER is there a better way to do this?  I don't understand another way to fix this problem!

Thanks for your time!

%macro Excel_macro(excelvariable,macrovariable);

OPTIONS NOXWAIT NOXSYNC;

X '"C:\Program Files\Microsoft Office\Office14\EXCEL.EXE"';

DATA _NULL_;

     VAR1=SLEEP(1);

RUN;

  OPTIONS NOXWAIT XSYNC;

FILENAME xcel DDE "EXCEL|SYSTEM";

DATA _NULL_;

     VAR1=SLEEP(2);

RUN;

DATA _NULL_;

     FILE xcel;

     put  %unquote(%str(%'[open(&excelvariable)]%'));

     put  %unquote(%str(%'[run(&macrovariable)]%'));

     PUT "[quit()]";

RUN;

data _null_;

   VAR1=SLEEP(2);

RUN;

 

filename programs pipe "tasklist /svc";

data want;

  infile programs truncover;

  informat open_programs $80.;

  input open_programs;

  if open_programs eq "EXCEL.EXE" then output;

run;

%let dsid1=%sysfunc(open(want));

%let excelopen=%sysfunc(attrn(&dsid1,nlobs));

%let rc=%sysfunc(close(&dsid1));

%if &excelopen ~=0 %then %do;

  DATA _NULL_;

     FILE xcel;

     PUT '[quit()]';

RUN;

%end;

proc datasets;

delete Want;

run;

%mend Excel_macro;


Accepted Solutions
Solution
‎09-03-2014 09:03 PM
Super User
Super User
Posts: 7,076

Re: Help re-writing macro

Posted in reply to Anotherdream

What do you actually need to write to the Excel command file in this situation?

Since you already have a data step I would take all of the macro logic out of the problem.

Does this generate the right code?

%let excelvariable="C:\company\brandon's files\MultiTabs.xlsm";

%let macrovariable="MultiTabs.xlsm!MultiTabsToCSV.MultiTabsToCSV";

DATA _NULL_;

   FILE xcel;

   open = cats('[open(',symget('excelvariable'),')]');

   run = cats('[run(',symget('macrovariable'),')]');

   put open / run / '[quit()]' ;

RUN;

[open("C:\company\brandon's files\MultiTabs.xlsm")]

[run("MultiTabs.xlsm!MultiTabsToCSV.MultiTabsToCSV")]

[quit()]

View solution in original post


All Replies
SAS Employee
Posts: 232

Re: Help re-writing macro

Posted in reply to Anotherdream

Hi Anotherdream , I've moved your question to the Macro community here to increase visibility of your question. Thanks!

Solution
‎09-03-2014 09:03 PM
Super User
Super User
Posts: 7,076

Re: Help re-writing macro

Posted in reply to Anotherdream

What do you actually need to write to the Excel command file in this situation?

Since you already have a data step I would take all of the macro logic out of the problem.

Does this generate the right code?

%let excelvariable="C:\company\brandon's files\MultiTabs.xlsm";

%let macrovariable="MultiTabs.xlsm!MultiTabsToCSV.MultiTabsToCSV";

DATA _NULL_;

   FILE xcel;

   open = cats('[open(',symget('excelvariable'),')]');

   run = cats('[run(',symget('macrovariable'),')]');

   put open / run / '[quit()]' ;

RUN;

[open("C:\company\brandon's files\MultiTabs.xlsm")]

[run("MultiTabs.xlsm!MultiTabsToCSV.MultiTabsToCSV")]

[quit()]

Super User
Super User
Posts: 7,988

Re: Help re-writing macro

Posted in reply to Anotherdream

Just to add, your problem lies in the chap Brandon using special characters in the pathname.  Me personally, I would fix that rather than try to code round it.  You should have some sort of company policy on folder naming, e.g. only alphanumeric and underscores (instead of spaces).  It will make your life a lot easier.  Reason is if you fix it for one quote, next week someone else will turn up and want rob"myfolder"2's or something similar.  Stop the problem at source.

Super User
Posts: 7,854

Re: Help re-writing macro

I strongly second what RW9 said.

---------------------------------------------------------------------------------------------
Maxims of Maximally Efficient SAS Programmers
Super Contributor
Posts: 418

Re: Help re-writing macro

Posted in reply to KurtBremser

Hey RW9 and Kurt.  I STRONGLY agree with you that this is ideal, however I have no control over what other people in my company name their files.

I wish I did, but I sadly just don't.

Tom, your method worked great.... Can you explain to me why this works and what I have doesn't? I understand that symget grags the actual macro variable, howver since the macro varaible has a single quote in it, it seems to me like the program should have made it into a string, causing errors (since that's what happens in my code).

Why does calling it in Symget cause it to work, while the other does not?

Thanks!

Super User
Super User
Posts: 7,988

Re: Help re-writing macro

Posted in reply to Anotherdream

Me personally I would have at minimum, an internal structure for your group which you control, and data which needs to be used would then come in via your group and be put onto your setup, otherwise your could be in problems.  I.e. what happens if they delete/rename, change folders, don't update to latest version of file etc.  If it was my process, I would write a document with an expectation of what files should be sent, what the structure of the files should be, delivery times etc.  My area would then be accessible only to me, and would be version controlled with a set import/validation phase to ensure the process is correctly implemented.  Plus I would want CSV data rather than Excel.

Super Contributor
Posts: 418

Re: Help re-writing macro

Hello RW9. Again I agree with you, however I was tasked with helping other teams deal wit hthe messes of files that they have, using their existing folder structures.

We cant' change the folder structures because they have many other codes / applications / etc.. that point ot them.  SO that's pretty much out of the question.

I understand we could move the files to a different location, etc..  But in my opinion working around the problem doesn't help my understanding of the problem to begin with.

So I understand that it shouldn't be necesssary to solve the problem i'm trying to solve, BUT it can be (and since i'm coding a macro library, other users don't have to follow any kind of logical convention, so I want macros' that work in as many circumstances as possible).

That's why I asked for the help, if that makes sense!

Super User
Super User
Posts: 7,076

Re: Help re-writing macro

Posted in reply to Anotherdream

It is mainly the difference in using macro coding to generate SAS code and just writing SAS code directly.

In your program you are trying to get the macro code to generate a statement like:

PUT 'string' ;

In my program I am using SAS code to generate a variable that has the value I want and then I use

PUT variable;

to write the value.

Since you tried to wrap the string in single quotes any embedded single quotes would need to be doubled to generate a valid string literal value that you could use in a PUT statement.

You could have instead used %SYSFUNC() to call the QUOTE() function.  This will generated a string literal quoted with double quote characters and any embedded double quote characters will be automatically doubled up for you.

     put  %sysfunc(quote([open(&excelvariable)]));

     put  %sysfunc(quote([run(&macrovariable)]));

🔒 This topic is solved and locked.

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

Discussion stats
  • 8 replies
  • 441 views
  • 1 like
  • 5 in conversation