I'm not sure if you're still dealing with this, but an approach that will resolve the issue with null macro variables and trailing commas is just to query all of your "a" vars from metadata. You know that customer and year will always be there, so the only question is the others:
proc sql noprint;
SELECT cats('a.', name) INTO :dynamicSelectVars separated by ','
FROM sashelp.vcolumn
WHERE libname = 'WORK' and
memname = 'FINAL_OUTPUT' and
lowcase(name) in ('customer', 'year', 'x1','y1','z1');
quit;
%put &dynamicSelectVars;
proc sql ;
create table final_table AS
select &dynamicSelectVars
, b.*
, c.*
from work.final_output as a
inner join prod.scores as b on a.id = b.id
inner join prod.city as c on a.business_id=c.id
quit;
... View more