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);
Editor's note: This answer is correct but if you need the commas to be part of the value you would say:
%prod(nbiz=cmus, biz=%str(4,14))
The following is also a link to the macro documentation that discusses macro parameters that you might find helpful:
As you have seen, commas separate parameters when calling a macro. Thus the commas that are NOT separating parameters cause confusion for macro language.
The simplest solution is to get rid of the extra commas in this case:
%prod(NBIZ=CMUS, biz= 4 14)
When substituted into the WHERE clause, the commas are optional. So removing the commas won't hurt, and makes the macro calls simpler.
Editor's note: This answer is correct but if you need the commas to be part of the value you would say:
%prod(nbiz=cmus, biz=%str(4,14))
The following is also a link to the macro documentation that discusses macro parameters that you might find helpful:
As you have seen, commas separate parameters when calling a macro. Thus the commas that are NOT separating parameters cause confusion for macro language.
The simplest solution is to get rid of the extra commas in this case:
%prod(NBIZ=CMUS, biz= 4 14)
When substituted into the WHERE clause, the commas are optional. So removing the commas won't hurt, and makes the macro calls simpler.
Hi,
Thanks for your help.
I still could not seem to get the query to work after I remove the comma. See the error below.
ERROR: ORACLE prepare error: ORA-00907: missing right parenthesis. SQL statement: 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 (5 15).
NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.
NOTE: Statement not executed due to NOEXEC option.
NOTE: Statement not executed due to NOEXEC option.
%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);
Yes, it's possible that you will have many errors to debug. This first step was only to get the macro to run at all.
Here's a way you can isolate the errors one at a time, so you can fix them.
Remove the bottom SELECT statements. Get the first CONNECT/SELECT/DISCONNECT group to work. You don't even need macro language to do this. Just take the code out of the macro, and hard-code the NBIZ and BUSINESS_UNIT value(s). Once that section of code is working, add in the next CONNECT/SELECT/DISCONNECT group. By removing the macro language in order to debug, the error messages will become much clearer. SAS will do a better job locating the source of the error.
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
Learn how use the CAT functions in SAS to join values from multiple variables into a single value.
Find more tutorials on the SAS Users YouTube channel.