DATA Step, Macro, Functions and more

PROC SQL and MACROS

Accepted Solution Solved
Reply
Contributor
Posts: 48
Accepted Solution

PROC SQL and MACROS

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


Accepted Solutions
Solution
‎02-03-2017 01:37 AM
Super User
Posts: 17,829

Re: PROC SQL and MACROS

Use QUOTE function to add quotes, instead of CATT

 

 

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

from ....

 

View solution in original post


All Replies
Super User
Posts: 17,829

Re: PROC SQL and MACROS

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?

Super User
Super User
Posts: 7,401

Re: PROC SQL and MACROS

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;
Super User
Posts: 5,082

Re: PROC SQL and MACROS

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.

Super Contributor
Posts: 336

Re: PROC SQL and MACROS

%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.***;
Solution
‎02-03-2017 01:37 AM
Super User
Posts: 17,829

Re: PROC SQL and MACROS

Use QUOTE function to add quotes, instead of CATT

 

 

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

from ....

 

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 5 replies
  • 181 views
  • 1 like
  • 5 in conversation