BookmarkSubscribeRSS Feed
Jamie_H
Fluorite | Level 6

I have a macro variable that holds a string.  The string is a clause that will be used in a procedure, eg; where x = &clause.

 

The string can hold one or many clauses:

Example 1 &clause. = Company A

Example 2 &clause. = Company A, Company B, Company C

 

Does anyone know how I can transform the macro variable to wrap quotes around each of the clauses, so I end up with this:

 

Example 1 &clause. = "Company A"

Example 2 &clause. = "Company A", "Company B", "Company C" 

 

Thanks.

10 REPLIES 10
Gireesh
Calcite | Level 5

If you are assigning the value in a %let statement, you can simply use the statement below:

%let clause = "Company A", "Company B", "Company C" ;
Best Regards,
Gireesh S
Tom
Super User Tom
Super User

You should be able to make the transition just using the TRANWRD() function.

%let list=Company A,Company B,Company C;
%let clause = "%sysfunc(tranwrd(%superq(list),%str(,),%str(",")))" ;
%put &=list &=clause;

Note that if you have spaces around your delimiter (commas in this example) that the spaces will become part of the quoted strings. Also note that using comma as your delimiter make it harder. You need to quote the commas in the source string so that TRANWRD() doesn't see too many words and quote the comma in the second argument to the TRANWRD() function.

 

You also need to worry about there being embedded delimiters into your string.  If that can happen then you probably need to use a more complex program that could use the modifiers of the SCAN() function to allow quoted strings to mask embedded delimiters.

ErikLund_Jensen
Rhodochrosite | Level 12

Hi Jamie_H

 

I think this macro, which can be used as a function, does whar you want:

 

%let clause1 = Company A;

%let clause2 = Company A, Company B, Company C;

 

%macro m(arg);

      %local clause wclause;

      %let ccnt = %eval(%sysfunc(countw(&arg,',')));

      %do i = 1 %to &ccnt;

            %let wclause = %scan(&arg,&i,%str(,));

            %let wclause = "&wclause";

            %if &i < &ccnt %then %let wclause = %quote(&wclause%str(,));

            %let clause = &clause &wclause;

      %end;

      &clause

%mend;

 

%let clause2 = %quote(%m(%quote(&clause2)));

%put &clause2;

Jamie_H
Fluorite | Level 6

Hi ErikLund,


Many thanks for this, it works perfectly.

 

Just a quick secondary question.  For a couple of variants, I need to adopt the code to include a "%" for a wildcard, so

%let clause2 = Company A, Company B, Company C    

becomes

clause2 = "Company A%", "Company B%", "Company C%"

 

I've tried playing with the code to insert this in a relevant place, but keep getting errors such as my quotes are uneven.  I'm guessing it is because a % is a special character and needs to be treated with care.


Any thoughts?

 

Thank you.

ballardw
Super User

@Jamie_H wrote:

Hi ErikLund,


Many thanks for this, it works perfectly.

 

Just a quick secondary question.  For a couple of variants, I need to adopt the code to include a "%" for a wildcard, so

%let clause2 = Company A, Company B, Company C    

becomes

clause2 = "Company A%", "Company B%", "Company C%"

 

I've tried playing with the code to insert this in a relevant place, but keep getting errors such as my quotes are uneven.  I'm guessing it is because a % is a special character and needs to be treated with care.


Any thoughts?

 

Thank you.


You should show the code where you are using that Clause2. Depending on how you are using it there are likely different approaches needed.

 

BTW I have had very few good results over the years when including quotes in the values of macro variables.

Shmuel
Garnet | Level 18

You may try next code:

 

data _NULL_;

    clause = tranw( "&clause" , ',' , '","');  

   call symput('clause','"'||claus||'"');

run;

 

Pay attention:   '"' is: single-double-single quotes.

  

Quentin
Super User

I'n a fan of Richard DeVenzia's utility macro %seplist.  It's very useful for taking a list, and adding or removind delimiters, quotes, etc.  You can download it from: http://www.devenezia.com/downloads/sas/macros/index.php?m=seplist

 

For your two examples, you can use like:

175  %let clause=Company A,Company B,Company C;
176
177  %put %seplist(%bquote(&clause),indlm=%str(,),dlm=%str(, ),nest=QQ);
"Company A", "Company B", "Company C"
178
179  %put %seplist(%bquote(&clause),indlm=%str(,),dlm=%str(, ),prefix=%str(%"),suffix=%str(%%%"));
"Company A%", "Company B%", "Company C%"
BASUG is hosting free webinars Next up: Jane Eslinger presenting PROC REPORT and the ODS EXCEL destination on Mar 27 at noon ET. Register now at the Boston Area SAS Users Group event page: https://www.basug.org/events.
Jamie_H
Fluorite | Level 6

Many thanks for your continued help Erik and Quentin.

 

I love that macro to separate out strings Quentin, and i'm sure that is going to prove incredibly useful to me. 

 

The final issue i'm having, is that the string with the wildcard % needs to be used in a as a LIKE in a Where clause of a Proc Sort.

 

When I use that macro to create the correct text, it gives me a syntax error.   The Macro variable itself resolves to a correct state - that being I can copy and paste the resolved text into the same Proc Sort and it will work.  

 

The code looks like this, with the error message afterwards.  Any suggestions?

 

%Let clause2 = COMPANYA,COMPANYB

%let clause2  = %seplist(%bquote(&clause2),indlm=%str(,),dlm=%str( or upcase(RetailerCode) like ),prefix=%str(%"),suffix=%str(%%%"));


proc sort data=Have out= Want nodupkey;
by variable_a;

where upcase(RetailerCode) like

&Clause2.

;
run;

--------

 

LOG OUTPUT


22         where upcase(RetailerCode) like
23        
24         &clause2.
25        
26         ;
NOTE: Line generated by the macro variable "Clause2".
26           "COMPANYA%" or upcase(RetailerCode) like "COMPANYB%"
             _
             22
              _
              76
ERROR: Syntax error while parsing WHERE clause.
ERROR 22-322: Syntax error, expecting one of the following: a name, a quoted string, a numeric constant, a datetime constant,
              a missing value, (, +, -, INPUT, NOT, PUT, ^, ~. 
ERROR 76-322: Syntax error, statement will be ignored.
27         run;

Patrick
Opal | Level 21

@Jamie_H

I believe for what you're after it would be much easier to use the IN operator with a column modifier.

Below code illustrates what I'm talking about.

%let clause2 = alf ,ali;

%let clause2Quotes=%sysfunc( prxchange(s/([^\s,]+)/"\1"/,-1,%nrbquote(&clause2)) );

%put clause2Quotes:         %nrbquote(&clause2Quotes);

proc print data=sashelp.class;
  where upcase(name) in: %upcase((&clause2Quotes))
  ;
run;
Quentin
Super User

In the end of the macro definition where it returns the value:

&emit

Try changing it to:

 

%unquote(&emit)

 

There are known problems in the macro language where macro quoting is not automatically removed.  In the setting you describe (MPRINT code looks correct and can even cut-and-paste from the log and run it), always try %unquoting().

BASUG is hosting free webinars Next up: Jane Eslinger presenting PROC REPORT and the ODS EXCEL destination on Mar 27 at noon ET. Register now at the Boston Area SAS Users Group event page: https://www.basug.org/events.

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
  • 10 replies
  • 6774 views
  • 0 likes
  • 8 in conversation