topic Re: Transform macro variable to wrap " " around words within in SAS Programming

Transform macro variable to wrap " " around words within

Jamie_H — Thu, 01 Jun 2017 16:00:30 GMT

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.

Re: Transform macro variable to wrap " " around words within

Gireesh — Thu, 01 Jun 2017 16:09:18 GMT

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

Re: Transform macro variable to wrap " " around words within

Tom — Thu, 01 Jun 2017 16:22:21 GMT

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.

Re: Transform macro variable to wrap " " around words within

ErikLund_Jensen — Thu, 01 Jun 2017 16:34:50 GMT

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;

Re: Transform macro variable to wrap " " around words within

Shmuel — Thu, 01 Jun 2017 16:48:44 GMT

You may try next code:

data _NULL_;

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

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

run;

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

Re: Transform macro variable to wrap " " around words within

Jamie_H — Fri, 02 Jun 2017 11:13:02 GMT

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.

Re: Transform macro variable to wrap " " around words within

ballardw — Fri, 02 Jun 2017 23:16:32 GMT

@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.

Re: Transform macro variable to wrap " " around words within

Quentin — Sat, 03 Jun 2017 01:12:21 GMT

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%"

Re: Transform macro variable to wrap " " around words within

Jamie_H — Tue, 06 Jun 2017 12:32:54 GMT

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;

Re: Transform macro variable to wrap " " around words within

Patrick — Tue, 06 Jun 2017 13:16:21 GMT

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

Re: Transform macro variable to wrap " " around words within

Quentin — Tue, 06 Jun 2017 13:27:18 GMT

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().