String list as Macro Parameter

[daveryBBW]

Good Afternoon!

I have list of parameters that I am trying to pass into a macro as a single parameter. I'm using %STR() around the variable since it is comma separated but for some reason it's still seeing the commas in the variable.

%let sc = '1224','1612','9603','9919','1920';

%macro sb_metrics(sb=%str('9999'), sb_txt_shrt=none, sb_txt_lng=none);
, count(distinct case when C.itm_sbrnd_cd in (&sb) then A.lbi_indv_id else . end)&factor as &sb_txt_shrt._indv_cnt
/* more code here but same concept */.
&factor as &sb_txt_shrt._indv_cnt
%mend sb_metrics;

proc sql;
create table Restage_Results_&Theme_TY_Name as
select
/* more code here but this functions normally */
%sb_metrics(sb=%str(&sc), sb_txt_shrt=sc, sb_txt_lng=Signature)
from perm.trans_&Theme_TY_Name._B as A
left outer join Restage as B on A.lgcy_sto_nbr = B.Store
join perm.sku_xref as C on A.SKU_XREF_ID = C.sku_xref_id
left outer join deciles_&Theme_TY_Name as D on A.lbi_indv_id = D.lbi_indv_id
group by 1
;
quit;

Here is the error I keep getting.

ERROR: All positional parameters must precede keyword parameters.
574 %sb_metrics(sb=%str(&sc), sb_txt_shrt=sc, sb_txt_lng=Signature)
-
79
ERROR 79-322: Expecting a (.

I've used this type of coding proceedure before and it's worked (I was using the old one as an example) but for some reason I just can't seem to get it to work.

Any suggestions would be greatly appreciated! Thank you!!

[sbb]

One option is to enclose your %LET SC = assignment statement with %STR( ) -- rather than on the macro invocation statement.

Scott Barry

[Peter_C]

have a look at the macro option parmbuff
It fills the macro variable SYSPBUFF with the macro parameter list (including the parentheses)

%macro sb_metrics(sb=%str('9999'), sb_txt_shrt=none, sb_txt_lng=none)
/parmbuff;
%put %quote(&syspbuff) ;


PeterC

[daveryBBW]

Scott - I thought about that but the variable is also being used to go into a SAS CONNECT query to another DBMS, which would throw an error at the %STR() function, since it's SAS and not native to the DBMS.

PeterC - Thank you - I'd never seen that option before, being a self taught macro coder. When I tested it though, it appears that because of how I'm using the macro to write lines within my PROC SQL statement instead of being around the PROC SQL, the code is erroring out before it gets to the part of printing the &syspbuff to the log. So, nothing is going to the log. I do intend to use this parameter in debugging other macro code though!

Apart from this post, I was doing some digging on another topic and think I might have found something that works in this case to fix my issue. I was reading somewhere where the person used %QUOTE() in conjunction with %UNQUOTE() for something else, so I tried it. I used %QUOTE() in the macro call and %UNQUOTE in the macro itself and when executed the code didn't throw any errors as of yet. The log shows it still running so I'll have to see how it finishes.

[data_null__]

You can avoid the use of QUOTING functions in this case with the use of parenthesis(). Notice the change in your code where &SB is used in the macro, I removed the parenthesis because they are now part of the value.

If you had a situation where the parenthiesis were not needed they could be removed with %qsubstr(%superq(sb),2,%length(%superq(sb))-2);


[pre]
433 %let sc = '1224','1612','9603','9919','1920';
434 %let FACTOR = ?; **Where is this?;
435
436 %macro sb_metrics(sb=('9999'), sb_txt_shrt=none,
436! sb_txt_lng=none);
437 %put _LOCAL_;
438 %put , count(distinct case when C.itm_sbrnd_cd in &sb
438! then A.lbi_indv_id else . end)&factor as
438! &sb_txt_shrt._indv_cnt
439 /* more code here but same concept */.
440 &factor as &sb_txt_shrt._indv_cnt
441 ;
442 %mend sb_metrics;
443
444 %sb_metrics(sb=(&sc))
SB_METRICS SB_TXT_SHRT none
SB_METRICS SB_TXT_LNG none
SB_METRICS SB ('1224','1612','9603','9919','1920')
, count(distinct case when C.itm_sbrnd_cd in
('1224','1612','9603','9919','1920') then A.lbi_indv_id else .
end)? as none_indv_cnt . ? as none_indv_cnt
[pre]

[PatrickG]

This looks like a timing thing, so try using a %bquote instead of %str e.g.

%let sc = '1224','1612','9603','9919','1920';
%sb_metrics(sb=%bquote(&sc), etc, etc, etc);

The %bquote masks the same items as %str, but %str masks constant text instead of a resolved value. The %str works during compilation time but the
%bquote works during execution time.