topic PROC SQL and MACROS in SAS Programming

PROC SQL and MACROS

sandeep_reddy — Thu, 02 Feb 2017 11:49:06 GMT

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

Re: PROC SQL and MACROS

Reeza — Thu, 02 Feb 2017 12:01:05 GMT

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?

Re: PROC SQL and MACROS

RW9 — Thu, 02 Feb 2017 12:13:44 GMT

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;

Re: PROC SQL and MACROS

Astounding — Thu, 02 Feb 2017 12:16:40 GMT

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.

Re: PROC SQL and MACROS

user24feb — Thu, 02 Feb 2017 13:13:05 GMT

%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.***;

Re: PROC SQL and MACROS

Reeza — Thu, 02 Feb 2017 14:11:08 GMT

Use QUOTE function to add quotes, instead of CATT

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

from ....