BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.

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

1 ACCEPTED SOLUTION

Accepted Solutions
Reeza
Super User

Use QUOTE function to add quotes, instead of CATT

 

 

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

from ....

 

View solution in original post

5 REPLIES 5
Reeza
Super User

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
Diamond | Level 26 RW9
Diamond | Level 26

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
PROC Star

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
Barite | Level 11
%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
Super User

Use QUOTE function to add quotes, instead of CATT

 

 

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

from ....

 

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

How to Concatenate Values

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.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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