PROC SQL and MACROS

[sandeep_reddy]

Hello,

I have a macro variable with value as %let Alert=2017-01,2017-02,2017-03;

I need to resolve this macro value in proc sql code.

Example:

Proc sql;

select * from Alerts where alert in("&Alert");

quit;

But for using this macro i need to put single quotes to the value of a macro and also the macro value is dynamic and it depends upon number of records in a column (Alert).

Many Thanks In Advance For Your Reply

[Reeza]

What do you mean the macro variable is dynamic and how do the number of records matter?

Why the need for single quotes, are you using SQL Pass Thru?

[RW9]

Personally I rarely use macro variables for "data".  I would put your "data" in datasets, then use the data as you would any other data.  Macro has limitations, and coding with it is verbose and messy.  So:

%let Alert=2017-01,2017-02,2017-03;
/* this bit just converts the macro variable to a dataset */
data vals;
  do i=1 to countw("&alert.",",");
    item=scan("&alert.",i,",");
    output;
  end;
run;

proc sql;
  select  * 
  from    ALERTS 
  where   ALERT in(select ITEM from VALS);
quit;

[Astounding]

Actually, what you are saying is that the macro variable was built incorrectly.  When it was built, there should have been quotes around each item in the list.  One alternative is to fix it now.  Another is to go back to the process that creates it and fix it there.  There are other alternatives, as other posters have noted.

[user24feb]

%Let Alert=2017-01,2017-02,2017-03;

%Let Alert_Where=%BQuote(%Str(%')%Sysfunc(TranWrd(%BQuote(&Alert.),%Str(,),%Str(%', %')))%Str(%'));
%Put ***&Alert_Where.***;

* .. but don't do this, if it comes from a data set then use Proc SQL;
Data Dates;
  D='2017-01'; Output;
  D='2017-02'; Output;
  D='2017-03'; Output;
Run;
Proc SQL NoPrint;
  Select Catt("'",D,"'") Into :Alert_Where_SQL Separated By ',' From Dates;
Quit;
%Put ***&Alert_Where_SQL.***;

* .. else define Alert the way you need it in the first place;
%Let Alert_Comma='2017-01', '2017-02' ,'2017-03' ;
%Put ***&Alert_Comma.***;

[Reeza · Accepted solution]

Use QUOTE function to add quotes, instead of CATT

Select quote(d, "'") into :alert_list separated by ", "

from ....