SAS - proc sql for table update in a macro -
i have code tries update table in macro block using macro variables in sub-query. challenge have @ times of macro variables null, making code fail. how can set ignore when macro varible produces null value?
%macro update_bucket; %let macro_fudge = %nrstr(%mend); proc sql; %do i=1 %to &rows.; %do j=1 %to &max_comb.; %do k=1 %to &max_lgd_comb.; %do l=1 %to &max_pd_comb.; %let x = _&&k.lgd&i.; %let y = _&&l.pd&i.; %if &x. ^=ltv %then %do; update sbbook_rb_a set riskbucket = (select risk_bucket portfolio_split_d b a.businessgroup = b.portfolio , a.product2 = b._&j.subport , a.&&&x. >= b.min_lgd_driver_%left(&k.) /* @ times "a.&&&x." returns null value */ , a.&&&x. < b.max_lgd_driver_%left(&k.) , a.&&&y. >= b.min_pd_driver_%left(&l.) /* @ times "a.&&&y." returns null value */ , a.&&&y. < b.max_pd_driver_%left(&l.) , b.row_num = &i. ) riskbucket = ''; %end; %end; %end; %end; %end; quit; %mend; %update_bucket;
thank you
best practice in cases use parameters, not global macro variables. in case can give them default value, can test , skip loop (for example, give default value of 0).
%macro mymacro(param1=0, param2=0); ... %mend mymacro;
you should never rely on globals way did in code, regardless of this; macro variable used in macro should parameter macro, excepting global things paths (and then, prefer parameters). can pass global value parameter, should passed.
Comments
Post a Comment