Using SQL to create a macro variable with ";" in the values

[sam_sas2]

Hi, 

So I currently have a table(say, table1) with values like this, 

column_1

ABC;TOP

GEM;POT

YUS

YRS

and I tried to push these values into a macro using this statement, 

proc sql noprint;
select column_1
into: macro_1
from table1;
quit;

However, when I tried running %put &macro_1; I get the following error:

ERROR 180-322: Statement is not valid or it is used out of proper order.

I tried masking both column_1 and macro_1 with %str() but none of them worked. Could someone help me out with this? Thanks in advance! 🙂

[PaigeMiller · Accepted solution]

Try

%put %quote(&macro_1);

[Sajid01]

The error is due to presence of semicolon(;) within the variable value.
It will work if the semi colon is replaced.

[ballardw]

Not the most useful macro variable I've seen. If you only want one value (from the first record only as the current sql is written) it would be just as easy to do manually.

But @PaigeMiller's suggestion works for code as written.

If you intend to put more of the values into that macro variable you will want to seriously consider whether a semicolon is appropriate at all as you may be jumping through multiple hoops to use it. Other problems result when using commas for separating values.

It might help to show exactly how you expect to use that macro variable after it is created.

[sam_sas2]

Hi,
Sorry. But I forgot to add - <seperated by '|' > in the above code.
The purpose is to run a SIMILAR TO ('%(ABC|XYZ|QWE)%') in red shift.
The values for the SIMILAR TO are present in a table, and I felt this could be a faster way to create macro variable that holds the value in the format- '%(ABC|XYZ|QWE)%'

[ballardw]

---

@sam_sas2 wrote:
Hi,
Sorry. But I forgot to add - <seperated by '|' > in the above code.
The purpose is to run a SIMILAR TO ('%(ABC|XYZ|QWE)%') in red shift.
The values for the SIMILAR TO are present in a table, and I felt this could be a faster way to create macro variable that holds the value in the format- '%(ABC|XYZ|QWE)%'

---

That looks like you do not actually want the semicolons that are in your values. I would suggest using the Translate function to replace the ; with a | character.

Example:

data junk;
   input var $;
datalines4;
abc;pdq
zzx;ghq
rtv
;;;;

proc sql noprint;
   select translate(var,'|',';') into :macrovar separated by '|'
   from junk
   ;
run;

%put &macrovar.;

[Tom]

---

@sam_sas2 wrote:
Hi,
Sorry. But I forgot to add - <seperated by '|' > in the above code.
The purpose is to run a SIMILAR TO ('%(ABC|XYZ|QWE)%') in red shift.
The values for the SIMILAR TO are present in a table, and I felt this could be a faster way to create macro variable that holds the value in the format- '%(ABC|XYZ|QWE)%'

---

So if you have those 3 strings in 3 observations in a dataset. You can use SQL to build the middle of the string and then use %BQUOTE() to wrap in the other characters.

data list;
  input term $20.;
cards4;
ABC
XYZ
QWE
;;;;

proc sql noprint;
select term into :list separated by '|' from list;
quit;

%let list=%bquote('%(&list)%') ;

Now you can use that string in your pass through code:

proc sql;
connect to redshift ..... ;
select * from connection to redshift 
(
 select * from X where Y SIMILAR TO (&list)
);
quit; 

[Tom]

How are you planning to use the value in your real application?

If you really need that exact text in the macro variable then the easiest way to be sure the contents are macro quoted is to use %SUPERQ().

493   %let macro1=%superq(macro1);
494   %put &=macro1;
MACRO1=ABC;TOP

If you are planning to use the value as a string literal later then just add the actual quotes around the value when generating the macro variable.  Use single quotes if the values can include macro triggers & and %.

proc sql noprint;
select quote(trim(column_1),"'")
  into :macro_1
  from table1
;
quit;

[sam_sas2]

Hi Tom,
Thanks for the %superq func.
Also, I couldn't quite get what you meant by 'can include macro triggers & and %.'

[Tom]

The macro processor ignores text in strings that are bounded by single quotes, but processes text that is bounded by double quotes.  So if the string has an & then the macro processor might think  you are trying to reference a macro variable. Or if it has a % it might think you are trying to invoke a macro or macro function.

Example:

data have;
  input company $20.;
cards;
A&W
;

proc sql noprint;
select quote(trim(company))
  into :company1 trimmed 
  from have
;
quit;

%put &=company1;

proc sql noprint;
select quote(trim(company),"'") 
  into :company2 trimmed 
  from have
;
quit;

%put &=company2;

Log:

24   proc sql noprint;
25   select quote(trim(company))
26     into :company1 trimmed
27     from have
28   ;
29   quit;
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds


WARNING: Apparent symbolic reference W not resolved.
30
31   %put &=company1;
COMPANY1="A&W"
32
33   proc sql noprint;
34   select quote(trim(company),"'")
35     into :company2 trimmed
36     from have
37   ;
38   quit;
NOTE: PROCEDURE SQL used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds


39
40   %put &=company2;
COMPANY2='A&W'