topic Re: Using SQL to create a macro variable with ";" in the values in SAS Programming

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

sam_sas2 — Wed, 23 Jun 2021 19:02:53 GMT

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! 🙂

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

PaigeMiller — Wed, 23 Jun 2021 19:09:26 GMT

Try

%put %quote(&macro_1);

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

Sajid01 — Wed, 23 Jun 2021 20:42:24 GMT

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

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

ballardw — Wed, 23 Jun 2021 21:56:28 GMT

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.

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

sam_sas2 — Thu, 24 Jun 2021 11:34:30 GMT

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

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

Tom — Thu, 24 Jun 2021 12:09:06 GMT

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;

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

ballardw — Thu, 24 Jun 2021 15:51:02 GMT

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

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

sam_sas2 — Thu, 24 Jun 2021 17:57:04 GMT

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

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

Tom — Fri, 25 Jun 2021 03:53:06 GMT

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'

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

Tom — Fri, 25 Jun 2021 04:08:47 GMT

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