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
create table Restage_Results_&Theme_TY_Name as
/* 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
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)
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!!
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.
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);
433 %let sc = '1224','1612','9603','9919','1920';
434 %let FACTOR = ?; **Where is this?;
436 %macro sb_metrics(sb=('9999'), sb_txt_shrt=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
439 /* more code here but same concept */.
440 &factor as &sb_txt_shrt._indv_cnt
442 %mend sb_metrics;
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