Hi all,
I’m having trouble getting the macro below to work. The run is fine for these first three passes:
%prod(NBIZ=RLS, biz=1);
%prod(NBIZ=PFS, biz=2);
%prod(NBIZ=RLE, biz=3);
But I’m running into an error when I try to run these two passes:
%prod(NBIZ=CMUS, biz= 4,14);
%prod(NBIZ=CMCA, biz= 5,15);
68 %prod(NBIZ=CMUS, biz= 4,14);
ERROR: All positional parameters must precede keyword parameters.
69 %prod(NBIZ=CMCA, biz= 5,15);
ERROR: All positional parameters must precede keyword parameters.
I'm having trouble figuring out how to correct this. Any assistance will be greatly appreciated. Thanks!
%macro prod (NBIZ=,biz=);
proc sql;
drop table ora.&NBIZ._PRODL1;
connect to oracle (user=polodm password=polodm path='K6PPOLDM');
create table ora.&NBIZ._PRODL1 as
select *
from connection to oracle (
SELECT DISTINCT business_unit,
CASE WHEN group_div = 999999 THEN group_div ELSE group_div || '0' || business_unit END AS group_div,
group_div_desc
FROM mbs.sas_sku_xref_vw
WHERE business_unit in (&biz.));
disconnect from oracle;
connect to oracle (user=polodm password=polodm path='K6PPOLDM');
create table ora.&NBIZ._PRODL2 as
select *
from connection to oracle (
select DISTINCT business_unit,
case WHEN division = 999999 THEN division ELSE division || '0' || business_unit END AS division, division_desc
from mbs.sas_sku_xref_vw
WHERE business_unit in (&biz.));
disconnect from oracle;
connect to oracle (user=polodm password=polodm path='K6PPOLDM');
create table ora.&NBIZ._PRODL3 as
select *
from connection to oracle (
SELECT DISTINCT business_unit,
CASE WHEN department = 999999 then department else department || '0' || business_unit end as department, department_desc
FROM mbs.sas_sku_xref_vw
WHERE business_unit in (&biz.));
disconnect from oracle;
connect to oracle (user=polodm password=polodm path='K6PPOLDM');
create table ora.&NBIZ._PRODL4 as
select *
from connection to oracle (
SELECT DISTINCT business_unit,
CASE WHEN sub_department = 999999 THEN sub_department ELSE sub_department || '0' || business_unit END AS sub_department, sub_department_desc
FROM mbs.sas_sku_xref_vw
WHERE business_unit in (&biz.));
disconnect from oracle;quit;
%mend;
%prod(NBIZ=RLS, biz=1);
%prod(NBIZ=PFS, biz=2);
%prod(NBIZ=RLE, biz=3);
%prod(NBIZ=CMUS, biz= 4,14);
%prod(NBIZ=CMCA, biz= 5,15);
... View more