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

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."")]";



1 ACCEPTED SOLUTION

Accepted Solutions
Tom
Super User Tom
Super User

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);

View solution in original post

7 REPLIES 7
Anotherdream
Quartz | Level 8

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!

ballardw
Super User

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)]%'));

Tom
Super User Tom
Super User

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;

jakarman
Barite | Level 11

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.   

---->-- ja karman --<-----
Tom
Super User Tom
Super User

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);

Anotherdream
Quartz | Level 8

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!

Tom
Super User Tom
Super User

%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);

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!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

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
  • 1088 views
  • 0 likes
  • 4 in conversation