02-02-2017 06:49 AM
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.
select * from Alerts where alert in("&Alert");
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
02-02-2017 07:01 AM
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?
02-02-2017 07:13 AM
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;
02-02-2017 07:16 AM
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.
02-02-2017 08:13 AM
%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.***;
Need further help from the community? Please ask a new question.