topic Re: Help with masking macro quotes in SAS Programming

Help with masking macro quotes

Anotherdream — Fri, 30 Aug 2013 21:15:02 GMT

Hello everyone. I need some help with understanding the masking of specific characters being passed into Macro's. This is actually related to a problem that Tom helped me solve the other day, however it has arisen in a seperate problem (I will use the first for simplicity).

Okay, the following code does not work. the reason is the FIRST macro input has a "(" within the name, so when sas Reads the macro (in the call step) it is reading an open parenthesis, and it is thus assuming macro variable 1 and 2 are the same (since it does not find a closed parenthesis).

%macro Mapping_Names(dsin,dsout);

data &dsout.;

infile "&dsin." dsd lrecl=32000 truncover obs=1;

length varnumCSV 8 name $2000.;

input @;

do varnumCSV = 1 to countw(_infile_,',','Q');

input name @;

output;

end;

run;

%mend Mapping_names;

%Mapping_Names(tab verification(((Greengiant Population, outputdata);

So basically I want to pass the entire first string (up until the comma) as a single string into sas, and I would like it to ignore the ((( values after the word Verification. This is because someone is naming files with these values (I can't change this). I also have written macro's that will open Excel files and then run the associated VBA within the Excel files (like the following) and someone named an excel with with a "(" in its title so I ran into the same error.

%macro runexcel_example(excelfile,macroname);

FILENAME xcel DDE "EXCEL|SYSTEM";

data _null_;

file xcel;

put "[open(""&excelfile."")]";

put "[open(""&macroname."")]";

put "[quit()]";

run;

%mend runexceL_example;

%runexcel_Example(I:\projects\multi(files.xlsm , multi(files.xlsm!Multifiles.multifiles);

Note that this macro program works for every macro we have, except for when the names have special characters (namly these parenthesis). Is there a way to hide these values and have sas put them directly into the file so it doesn't combine both macro varaibles into 1 variable and blow up the program?

I am thinking something along the lines of the %str, or %nstr, or %quote values but I have not been able to figure this one out!

Thanks!

Brandon

&macrovariable."")]";

Re: Help with masking macro quotes

Anotherdream — Fri, 30 Aug 2013 21:22:50 GMT

And if this is not possible, could anyone help me re-write the second program so that I can quote the macro variable itself but still get it into Macro form? Aka how would I get something along the lines of the following to work...

%macro runexcel_example(excelfile,macroname);

FILENAME xcel DDE "EXCEL|SYSTEM";

data _null_;

file xcel;

put "[open(&excelfile.)]";

put "[open(&macroname.)]";

put "[quit()]";

run;

%mend runexceL_example;

%runexcel_Example("I:\projects\multi(files.xlsm " , "multi(files.xlsm!Multifiles.multifiles");

This above code DOES NOT WORK, as I can't pass a macro string with double quotes into the put statement, and I'm sure there is a way to do this, but as of yet I cannot get it to work. If anyone can solve this it would be greatly appreciated!

Re: Help with masking macro quotes

ballardw — Fri, 30 Aug 2013 22:18:14 GMT

Here is some code I have used to pass macro values in DDE calls. Not the use of %' to mask quotes from the macro quoteing functions.

You may not want to have the macro period concatenaion indicator when not actually needing it for this purpose.

put %unquote(%str(%'[OPEN("&outpath.&&Sd&i SIR 2012 Summary.xml")]%'));

put '[ERROR("FALSE")]';

put %unquote(%str(%'[SAVE.AS("&Excelpath.&&Sd&i SIR 2012 Summary.xlsx",51)]%'));

Re: Help with masking macro quotes

Tom — Fri, 30 Aug 2013 22:18:38 GMT

In SAS when you want to express a literal that contains the quote character you double the quote character on the inside. For example:

answer = 'I don''t know' ;

The QUOTE() function will quote a string and will double any quote characters that it contains.

%let fname=The "big" file.xls ;

filename in %sysfunc(quote(&fname));

The $QUOTE. format will do the same for a character variable.

%macro runexcel_example(excelfile,macroname);

FILENAME xcel DDE "EXCEL|SYSTEM";

data _null_;

file xcel;

excelfile = symget('excelfile');

macroname = symget('macroname');

put '[open(' excelfile :$quote. +(-1) ')]';

put '[open(' macroname :$quote. +(-1) ')]';

put '[quit()]';

run;

%mend runexceL_example;

Re: Help with masking macro quotes

jakarman — Sat, 31 Aug 2013 09:17:22 GMT

anotherdream, I see you are desperately wanting to have it coded, that names, by using macro-language, but did you ever being thinking of doing it in some other approach?

The problem with all those special chars is caused by different interpretations while being passed in all involved language processors.

If you could bypass that and just passing it as datastep variables using symput/symget the cause is eliminted. -> no issues anymore.

SAS(R) 9.4 Companion for Windows (infile statement) A lot is possible with wildcards and using a feedback getting the name.

%quote( ) and %unquote( ) are sometimes needed but requirng some attention SAS(R) 9.4 Macro Language: Reference.

Re: Help with masking macro quotes

Tom — Sat, 31 Aug 2013 15:52:27 GMT

There are many macro quoting functions, for this example %SUPERQ() is probably the easiest.

Assuming that the macro variable NAME1 has the first filename that you want to pass to your macro for processing, you would write the macro call like this.

%Mapping_Names(%superq(name1), outputdata);

Re: Help with masking macro quotes

Anotherdream — Wed, 11 Sep 2013 15:24:41 GMT

Hello Tom! As an additional question to this, (your example using %superq worked for files with ( in their names)...

How would one go about creating a macro that reads in files that contain quotes within their names? Aka if you had a file named

Bob o'conner.csv, how would one go about readin this into a macro as below

%macro Mapping_Names(dsin,dsout);

data &dsout.;

infile &dsin. dsd lrecl=32000 truncover obs=1;

length varnumCSV 8 name $2000.;

input @;

do varnumCSV = 1 to countw(_infile_,',','Q');

input name @;

output;

end;

run;

%mend Mapping_names;

I know you can do %mapping_names("C:\users\Bob o'conner.csv",outputdataset), however if I wanted to specify Bob o'conner as a macro variable, I would have to double quote it and call the macro like following.

%let var1="C:\users\Bob o'conner.csv";

%Mapping_names(&var1,outputdataset).

My question is, what if I had a dataset that contained a list of 1000+ files that I wanted to loop through, and the double quotes did not exist around the datafile name? is there a quoteing function that adds double quotes to your macro variables for you? I'm pretty confident i've seen this before, but I can't get anything to work in this example... So i'm looking for something along the lines of .

%mapping_names(%quote(&var1),outputdataset);

Where var1 is assigned from a call symputx in a loop on a existing dataset!

Thanks again!

Re: Help with masking macro quotes

Tom — Wed, 11 Sep 2013 15:33:20 GMT

%SUPERQ() is useful when you have no idea what types of characters might be in your macro variable, which is common issue when the variable is generated from data via CALL SYMPUTX or PROC SQL INTO: syntax.

If you already have the VAR1 macro variable created then you can safely make it a quoted string like this:

%mapping_names(%sysfunc(quote(%superq(var1))),outputdataset);

You could also change the way VAR1 is generated. For example if you have the variable FILENAME in the dataset FILELIST.

data _null_;

set filelist;

call symputx(cats('var',_n_),quote(trim(filename)));

run;

...

%mapping_names(&var1,outputdataset);