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 ¯o_1; I get the following error:
Try
%put %quote(¯o_1);
The error is due to presence of semicolon(;) within the variable value.
It will work if the semi colon is replaced.
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 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 ¯ovar.;
@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;
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;
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'
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.