Multiple parameter selections in a stored process

[deleted_user]

I am trying to create code in a stored process for processing a multiple selection parameter and am not having luck. I keep receiving the error message, "ERROR: A character operand was found in the %EVAL function or %IF condition where a numeric operand is required. The condition was: %ethnicity0." This is followed by, "ERROR: The %TO value of the %DO I loop is invalid."

Here is the code that is causing the problem:

%local i or;
%if %superq(ethnicity) ne %str() %then %do;
%let ethnictiy1 = %superq(ethnicity);
%do i = 1 %to %ethnicity0;
%let ethnicval=&ethnicval ∨ "'%superq(ethnicity&i)'";
%let or = or;
%end;
%end;

ethnicity is the name of the multiple selection input parameter; ethnicval is the name of the macro value that I intend to use in my follow-on sql.

What I am trying for is to have a sql where clause built by my macro that will take my multiple choices and end up with a line like this:

If table.ethnic eq 'abc' or 'def' or ''ghi' or 'jkl';

What am I doing wrong? By the way, Cynthia, I looked at the links you provided earlier, but still don't seem to be getting things to work 100%.

If anyone is willing to assist, I would love it. Thanks!

[Cynthia_sas]

Hi...
First, you can't string your "OR" like that. Only these are valid: [pre]
If table.ethnic eq 'abc' or
table.ethnic eq 'def' or
table.ethnic eq 'ghi' or
table.ethnic eq 'jkl';

where table.ethnic eq 'abc' or
table.ethnic eq 'def' or
table.ethnic eq 'ghi' or
table.ethnic eq 'jkl';

where table.ethnic in ('abc', 'def', 'ghi', 'jkl');
[/pre]

So that's one problem. You don't really need superq for that first comparison when you are testing for the presence of the &ethnicity parameter (see program). Also, if you REQUIRED them to make at least 1 selection then you would know that ETHNICITY would always have a value and you would not have to code for 0 selections. And, you don't really need a macro variable for the OR condition anyway -- if you use the IN operator.

Here's some code (below) to test. My recommendation, at this point, would be to contact Tech Support for help with multiple selections. Multiple selections are hard enough to grasp if you know SAS Macro coding --whether you need SUPERQ, how to use SUPERQ, how to test for NULL values, when values will be NULL in regular code, what other characters need to be handled specially (logical operators, mismatched quotes, parentheses, etc), how to build a string, +more -- and I imagine the code might look like Greek if you're new to SAS programming and concepts AND new to Macro.

I like to think of the SAS Macro facility as an invisible "typewriter" whose only purpose in life is to "auto-type" code for me and send it to the SAS compiler. That is a simplistic explanation, but the whole purpose of the %testcode macro below is to generate these strings:
[pre]
"cat_herder","feather_collector" (for two selections)
"alligator_wader" (for one selction)
"report_writer" (my default if they make NO selections)
[/pre]

If you look at the final WHERE clause and the TITLE statement in the %testdata macro, you will see that I put the rest of my WHERE clause in the "whole" program macro, not in the "code snippet" macro. But, anyway, for the 2 selection situation, I have to generate the "beginning" of the choices:
"cat_herder"
then for the next choice, I have to generate a comma in front of the second, third and ... choices.
,"feather_collector"

when I'm done, with my %do loop, I will have this string in my newly constructed macro variable (let's say that it's called WHERECLS):
"cat_herder","feather_collector"

Then, later in my program, I only use &WHERECLS like this:
where ethnic IN (&wherecls);

This saves me from having to jump through hoops to protect the opening and closing parentheses. The same kind of thing goes on for the 1 selection and the 0 selection situation. Anyway, try this program. If this still doesn't help you, then you really need to get Tech Support to take a look at your real code and help you get it working.

cynthia
(ps I turned on symbolgen, mprint and mlogic, so you could look in the sas log and see how everything was being resolved. But, I recommend that you submit Step 1, look in the log. Then submit step 2, look in the log. compare the code inside the macros to the code that got executed in the log (you can tell the "resolved" macro statements in the log because they will all start with MLOGIC, MPRINT or SYMBOLGEN). SYMBOLGEN shows you the resolution of a single macro var and MPRINT shows you the resolved code and MLOGIC shows you the path through the %IF statements. Then submit step 3, then submit step 4. Review the log at every step.

[pre]
** step 1: create the macro programs and make some data;

data work.sillydata;
length name $8 ethnic $18;
infile datalines;
input name $ ethnic $ joblev ;
return;
datalines;
alma cat_herder 1
bob cat_herder 2
carl alligator_wader 2
dave feather_collector 1
edna report_writer 1
fred alligator_wader 3
george report_writer 2
harry teen_magician 2
ida spell_caster 3
;
run;

%macro testdata;
options mlogic mprint symbolgen;
** new macro to USE where clause with REAL data;
** I modified this from an old jobcode example, so it;
** is just your macro var names on a job code example.;

proc sql;
create table goteth as
select *
from work.sillydata
where ethnic IN (&wherecls);
quit;

proc print data=goteth;
title "Using %superq(wherecls) in SQL";
run;

%mend testdata;

%macro testcode;
%global ethnicity0 wherecls
ethnicity1 ethnicity2 ethnicity;
** the GLOBAL statement ensures that the macro variables are;
** in the GLOBAL symbol table, but will be NULL if no values;
** are sent from the server;
** this is the macro that only builds the WHERE clause;
** I could have put them together but I like to separate;
** out the "whole" macro programs from the ones that just;
** generate code snippets;
options mlogic mprint symbolgen;
** test for 0 selections -- ethnicity macro var would be null;
%if &ethnicity = %then %do;
%put --------> DO NOT DO THAT!;
%let ethnicity = report_writer;
%let ethnicity0 = ;
%end;
%if &ethnicity0 = %then %do;
** they only made 1 selection which is contained;
** in the ethnicity macro parameter;
%let wherecls = "%superq(ethnicity)";
%put ---> inside first IF;
%put ---> wherecls = &wherecls;
%put (work.ethnic IN (&wherecls));
%end; /* end 1 selection */
%else %do;
** they selected 2 or more;
** so the do loop can be used;
%put =====> inside ELSE;
%do i = 1 %to &ethnicity0;
%put &i ** &&ethnicity&i;
%if &i = 1 %then %let wherecls = "%superq(ethnicity&i)";
%else %let wherecls = &wherecls,"%superq(ethnicity&i)";
%end; /* end do loop */
** now have the whole string for all their selections;
%put wherecls is &wherecls;
%put what you get when you reference wherecls:;
%put (work.ethnic IN (&wherecls));
%end; /* end else condition */
%mend testcode;


** step 2: run the macro program and simulate that;
** they made 2 choices;
** simulate getting 2 or more parameters;
** if parameter is ethnicity, then for 2 selections;
** macro vars would be: ;
** ethnicity0 would be 2;
** ethnicity1 would be 1st selection;
** ethnicity2 would be 2nd selection;
** ethnicity would be one of the two selections;

%let wherecls = ;
%let ethnicity1 = cat_herder;
%let ethnicity2 = feather_collector;
%let ethnicity0 = 2;
%let ethnicity=feather_collector;
%testcode;
%testdata;

** step 3: Now simulate getting just 1 selection;
** if parameter is ethnicity, then for 1 selection;
** macro vars would be: ;
** ethnicity0 would be null;
** ethnicity1 would not be created;
** ethnicity2 would not be created;
** ethnicity would be THE single selection;


%symdel ethnicity1 ;
%symdel ethnicity2;
%let wherecls = ;
%let ethnicity0 =;
%let ethnicity=alligator_wader;
%testcode;
%testdata;

** step 4: Now simulate getting NO selection;
** if parameter is ethnicity, then for NO selection;
** macro vars would be: ;
** ethnicity would be null;
** ethnicity0 would be null;
** ethnicity1 would not be created;
** ethnicity2 would not be created;
** ethnicity would be THE single selection;
** note that my macro program sets ethnicity to report_writer;
** if there were NO selections;

%symdel ethnicity1 ;
%symdel ethnicity2;
%symdel ethnicity0;
%symdel ethnicity;
%let wherecls = ;
%testcode;
%testdata;
[/pre]

[deleted_user]

Hi, Cynthia, and thanks for the informative reply!! However, when I copied/pasted your suggested code and tried running it, I still got the same error as before. I've enclosed my copied/pasted source code and my log file output. If you have further ideas, I'd love to hear them. Also, am I going about asking my things in the right way? I only ask becuase I don't want to end up monopolizing anything. Thanks so much for all the help and time you have given me.

The source code:

data work.sillydata;
length name $8 ethnic $18;
infile datalines;
input name $ ethnic $ joblev;
return;
datalines;
alma cat_herder 1
bob cat_herder 2
carl alligator_wader 2
dave feather_collector 1
edna report_writer 1
fred alligator_wader 3
george report_writer 2
harry teen_magician 2
ida spell_caster 3
;
run;

%macro testdata;
options mlogic mprint symbolgen;
proc sql;
create table goteth as
select *
from work.sillydata
where ethnic in (&wherecls);
quit;

proc print data=goteth;
title "Using %superq(wherecls) in SQL";
run;
%mend testdata;

%macro testcode;
%global ethnicity0 wherecls
ethnicity1 ethnicity2 ethnicity3;
options mlogic mprint symbolgen;
%if &ethnicity = %then %do;
%put --------> DO NOT DO THAT!;
%let ethnicity = report_writer;
%let ethnicity0 = ;
%end;
%if &ethnicity0 = %then %do;
%let wherecls = "%superq(ethnicity)";
%put ---> inside first IF;
%put ---> wherecls = &wherecls;
%put (work.ethnic IN (&wherecls));
%end; /* end 1 selection */
%else %do;
%do i = 1 %to ðnicity0;
%put &i ** &&ethnicity&i;
%if &i = 1 %then %let wherecls = "%superq(ethnicity&i)";
%else %let wherecls = &wherecls,"%superq(ethnicity&i)";
%end; /* end do loop */
%put wherecls is &wherecls;
%put what you get when you reference wherecls:;
%put (work.ethnic IN (&wherecls));
%end; /* end else condition */
%mend testcode;

%let wherecls = ;
%let ethnicity1 = cat_herder;
%let ethnicity2 = feather_collector;
%let ethnicity0 = 2;
%let ethnicity=feather_collector;
%testcode;
%testdata;

%symdel ethnicity1 ;
%symdel ethnicity2;
%let wherecls = ;
%let ethnicity0 =;
%let ethnicity=alligator_wader;
%testcode;
%testdata;

%symdel ethnicity1 ;
%symdel ethnicity2;
%symdel ethnicity0;
%symdel ethnicity;
%let wherecls = ;
%testcode;
%testdata;

The log file:

NOTE: Copyright (c) 2002-2003 by SAS Institute Inc., Cary, NC, USA.
NOTE: SAS (r) 9.1 (TS1M3)
Licensed to ALAMO COMMUNITY COLLEGE DISTRICT, Site 0001571006.
NOTE: This session is executing on the NET_ASRV platform.



NOTE: SAS 9.1.3 Service Pack 3

WARNING: Only Base procedures and SAS/STAT procedures have been tested for use with VALIDVARNAME=ANY. Other use of this option is considered experimental and may cause undetected errors.

NOTE: SAS Initialization used (Total process time):
real time 0.01 seconds
cpu time 0.01 seconds


>>> SAS Macro Variables:

_RESULT=STREAM
_ENCODING=UTF-8
_APSLIST=_encoding,_gopt_device,_metaperson,_metauser,_odsdest,_odsstyle,_result,_url
_URL=.
_METAUSER=district\djackson20
_REPLAY="&_URL?_sessionid=7F0B99C9-1534-424E-8FAB-F5C129CE6A1C&_program=replay&_entry=&_TMPCAT.."
_METAPERSON=David Jackson
_ODSSTYLE=default
_GOPT_DEVICE=ACTIVEX
_ODSDEST=HTML
_TMPCAT=APSWORK.TCAT02DB

1 options nosource source2 center notes nodate nonumber ls=195 formchar='|----|+|---+=|-/\<>*' pagesize=40 noovp nomprint nomlogic nosymbolgen; title; footnote;
The SAS System

NOTE: %INCLUDE (level 1) file E:\ACCD Stored Processes\Test Stored Processes\Stored_Process1~16.sas is file E:\ACCD Stored Processes\Test Stored Processes\Stored_Process1~16.sas.
3 +/* Begin EG generated code (do not edit this line) */
4 +/* Application registered by
5 +Enterprise Guide Application Manager v1.3 */
6 +
7 +*ProcessBody;
8 +
9 +%stpbegin;
10 +
11 +/* End EG generated code (do not edit this line) */
12 +
13 +data work.sillydata;
14 +length name $8 ethnic $18;
15 + infile datalines;
16 + input name $ ethnic $ joblev;
17 + return;
18 +datalines;

NOTE: The data set WORK.SILLYDATA has 9 observations and 3 variables.
NOTE: DATA statement used (Total process time):
real time 0.03 seconds
cpu time 0.01 seconds


28 +;
29 +run;
30 +
31 +%macro testdata;
32 +options mlogic mprint symbolgen;
33 +proc sql;
34 +create table goteth as
35 + select *
36 + from work.sillydata
37 + where ethnic in (&wherecls);
38 + quit;
39 +
40 + proc print data=goteth;
41 + title "Using %superq(wherecls) in SQL";
The SAS System

42 + run;
43 + %mend testdata;
44 +
45 +%macro testcode;
46 +%global ethnicity0 wherecls
47 + ethnicity1 ethnicity2 ethnicity3;
48 +options mlogic mprint symbolgen;
49 +%if &ethnicity = %then %do;
50 + %put --------> DO NOT DO THAT!;
51 + %let ethnicity = report_writer;
52 + %let ethnicity0 = ;
53 +%end;
54 +%if &ethnicity0 = %then %do;
55 + %let wherecls = "%superq(ethnicity)";
56 + %put ---> inside first IF;
57 + %put ---> wherecls = &wherecls;
58 + %put (work.ethnic IN (&wherecls));
59 +%end; /* end 1 selection */
60 +%else %do;
61 + %do i = 1 %to ðnicity0;
62 + %put &i ** &&ethnicity&i;
63 + %if &i = 1 %then %let wherecls = "%superq(ethnicity&i)";
64 + %else %let wherecls = &wherecls,"%superq(ethnicity&i)";
65 + %end; /* end do loop */
66 + %put wherecls is &wherecls;
67 + %put what you get when you reference wherecls:;
68 + %put (work.ethnic IN (&wherecls));
69 +%end; /* end else condition */
70 +%mend testcode;
71 +
72 +%let wherecls = ;
73 +%let ethnicity1 = cat_herder;
74 +%let ethnicity2 = feather_collector;
75 +%let ethnicity0 = 2;
76 +%let ethnicity=feather_collector;
77 +%testcode;
MPRINT(TESTCODE): ;
SYMBOLGEN: Macro variable ETHNICITY resolves to feather_collector
The SAS System

MLOGIC(TESTCODE): %IF condition &ethnicity = is FALSE
SYMBOLGEN: Macro variable ETHNICITY0 resolves to 2
MLOGIC(TESTCODE): %IF condition &ethnicity0 = is FALSE
ERROR: A character operand was found in the %EVAL function or %IF condition where a numeric operand is required. The condition was: ðnicity0
ERROR: The %TO value of the %DO I loop is invalid.
ERROR: The macro TESTCODE will stop executing.
MLOGIC(TESTCODE): Ending execution.
78 +%testdata;
MLOGIC(TESTDATA): Beginning execution.
MPRINT(TESTDATA): options mlogic mprint symbolgen;
MPRINT(TESTDATA): proc sql;
NOTE: Line generated by the invoked macro "TESTDATA".
78 create table goteth as select * from work.sillydata where ethnic in (&wherecls); quit; proc print data=goteth; title "Using
_
22
78 ! %superq(wherecls) in SQL"; run;
SYMBOLGEN: Macro variable WHERECLS resolves to
MPRINT(TESTDATA): create table goteth as select * from work.sillydata where ethnic in ();
ERROR 22-322: Syntax error, expecting one of the following: a quoted string, a numeric constant, a datetime constant, a missing value.

NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.
MPRINT(TESTDATA): quit;
NOTE: The SAS System stopped processing this step because of errors.
NOTE: SAS set option OBS=0 and will continue to check statements. This may cause NOTE: No observations in data set.
NOTE: PROCEDURE SQL used (Total process time):
real time 0.00 seconds
cpu time 0.00 seconds


MPRINT(TESTDATA): proc print data=goteth;
ERROR: File WORK.GOTETH.DATA does not exist.
MPRINT(TESTDATA): title "Using in SQL";
MPRINT(TESTDATA): run;
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE PRINT used (Total process time):
real time 0.00 seconds
cpu time 0.00 seconds

The SAS System

MLOGIC(TESTDATA): Ending execution.
79 +
80 +%symdel ethnicity1 ;
81 +%symdel ethnicity2;
82 +%let wherecls = ;
83 +%let ethnicity0 =;
84 +%let ethnicity=alligator_wader;
85 +%testcode;
MLOGIC(TESTCODE): Beginning execution.
MLOGIC(TESTCODE): %GLOBAL ETHNICITY0 WHERECLS ETHNICITY1 ETHNICITY2 ETHNICITY3
MPRINT(TESTCODE): options mlogic mprint symbolgen;
SYMBOLGEN: Macro variable ETHNICITY resolves to alligator_wader
MLOGIC(TESTCODE): %IF condition &ethnicity = is FALSE
SYMBOLGEN: Macro variable ETHNICITY0 resolves to
MLOGIC(TESTCODE): %IF condition &ethnicity0 = is TRUE
MLOGIC(TESTCODE): %LET (variable name is WHERECLS)
MLOGIC(TESTCODE): %PUT ---> inside first IF
---> inside first IF
MLOGIC(TESTCODE): %PUT ---> wherecls = &wherecls
SYMBOLGEN: Macro variable WHERECLS resolves to "alligator_wader"
SYMBOLGEN: Some characters in the above value which were subject to macro quoting have been unquoted for printing.
---> wherecls = "alligator_wader"
MLOGIC(TESTCODE): %PUT (work.ethnic IN (&wherecls))
SYMBOLGEN: Macro variable WHERECLS resolves to "alligator_wader"
SYMBOLGEN: Some characters in the above value which were subject to macro quoting have been unquoted for printing.
(work.ethnic IN ("alligator_wader"))
MLOGIC(TESTCODE): Ending execution.
86 +%testdata;
MLOGIC(TESTDATA): Beginning execution.
MPRINT(TESTDATA): options mlogic mprint symbolgen;


MPRINT(TESTDATA): proc sql;
SYMBOLGEN: Macro variable WHERECLS resolves to "alligator_wader"
SYMBOLGEN: Some characters in the above value which were subject to macro quoting have been unquoted for printing.
MPRINT(TESTDATA): create table goteth as select * from work.sillydata where ethnic in ("alligator_wader");
NOTE: Table WORK.GOTETH created, with 0 rows and 3 columns.

The SAS System

MPRINT(TESTDATA): quit;
NOTE: PROCEDURE SQL used (Total process time):
real time 0.01 seconds
cpu time 0.01 seconds


MPRINT(TESTDATA): proc print data=goteth;
MPRINT(TESTDATA): title "Using "alligator_wader" in SQL";
MPRINT(TESTDATA): run;
NOTE: PROCEDURE PRINT used (Total process time):
real time 0.01 seconds
cpu time 0.01 seconds


MLOGIC(TESTDATA): Ending execution.
87 +
88 +%symdel ethnicity1 ;
89 +%symdel ethnicity2;
90 +%symdel ethnicity0;
91 +%symdel ethnicity;
92 +%let wherecls = ;
93 +%testcode;
MLOGIC(TESTCODE): Beginning execution.
MLOGIC(TESTCODE): %GLOBAL ETHNICITY0 WHERECLS ETHNICITY1 ETHNICITY2 ETHNICITY3
MPRINT(TESTCODE): options mlogic mprint symbolgen;
WARNING: Apparent symbolic reference ETHNICITY not resolved.
ERROR: A character operand was found in the %EVAL function or %IF condition where a numeric operand is required. The condition was: &ethnicity =
ERROR: The macro TESTCODE will stop executing.
MLOGIC(TESTCODE): Ending execution.
94 +%testdata;
MLOGIC(TESTDATA): Beginning execution.
MPRINT(TESTDATA): options mlogic mprint symbolgen;
MPRINT(TESTDATA): proc sql;
NOTE: Line generated by the invoked macro "TESTDATA".
94 create table goteth as select * from work.sillydata where ethnic in (&wherecls); quit; proc print data=goteth; title "Using
_
22
94 ! %superq(wherecls) in SQL"; run;
The SAS System

SYMBOLGEN: Macro variable WHERECLS resolves to
MPRINT(TESTDATA): create table goteth as select * from work.sillydata where ethnic in ();
ERROR 22-322: Syntax error, expecting one of the following: a quoted string, a numeric constant, a datetime constant, a missing value.

NOTE: PROC SQL set option NOEXEC and will continue to check the syntax of statements.
MPRINT(TESTDATA): quit;
NOTE: The SAS System stopped processing this step because of errors.
NOTE: PROCEDURE SQL used (Total process time):
real time 0.00 seconds
cpu time 0.00 seconds


MPRINT(TESTDATA): proc print data=goteth;
MPRINT(TESTDATA): title "Using in SQL";
MPRINT(TESTDATA): run;
NOTE: PROCEDURE PRINT used (Total process time):
real time 0.00 seconds
cpu time 0.00 seconds


MLOGIC(TESTDATA): Ending execution.
95 +
96 +/* Begin EG generated code (do not edit this line) */
97 +%stpend;
MLOGIC(STPEND): Beginning execution.
MLOGIC(STPEND): This macro was compiled from the autocall file C:\Program Files\SAS\SAS 9.1\inttech\sasmacro\stpend.sas
MLOGIC(STPEND): Parameter SHOWVER has value N
MLOGIC(STPEND): Parameter MIMEMAP has value
MLOGIC(STPEND): Parameter DEBUG has value
MLOGIC(STPEND): %GLOBAL _RESULT _STPERROR _STPERRMSG _STPWORK
MLOGIC(STPEND): %LOCAL DEBUG I QRESULT MIMEMAP MIMEMAP1 NOTES SHOWVER
MLOGIC(STPEND): %LET (variable name is NOTES)
MPRINT(STPEND): options nonotes;
MLOGIC(QCMPRES): Beginning execution.
MLOGIC(QCMPRES): This macro was compiled from the autocall file C:\Program Files\SAS\SAS 9.1\core\sasmacro\qcmpres.sas
SYMBOLGEN: Macro variable _STPERROR resolves to 0
MLOGIC(QCMPRES): Parameter TEXT has value 0
MLOGIC(QCMPRES): %LOCAL I
The SAS System

MLOGIC(QCMPRES): %LET (variable name is I)
SYMBOLGEN: Macro variable TEXT resolves to 0
SYMBOLGEN: Macro variable I resolves to 0
MLOGIC(QCMPRES): %DO %WHILE(&i^=0) loop beginning; condition is FALSE. Loop will not be executed.
MLOGIC(QLEFT): Beginning execution.
MLOGIC(QLEFT): This macro was compiled from the autocall file C:\Program Files\SAS\SAS 9.1\core\sasmacro\qleft.sas
MLOGIC(QTRIM): Beginning execution.
MLOGIC(QTRIM): This macro was compiled from the autocall file C:\Program Files\SAS\SAS 9.1\core\sasmacro\qtrim.sas
SYMBOLGEN: Macro variable TEXT resolves to 0
MLOGIC(QTRIM): Parameter VALUE has value 0
MLOGIC(QTRIM): %LOCAL I
SYMBOLGEN: Macro variable VALUE resolves to 0
MLOGIC(QTRIM): %DO loop beginning; index variable I; start value is 1; stop value is 1; by value is -1.
SYMBOLGEN: Macro variable VALUE resolves to 0
SYMBOLGEN: Macro variable I resolves to 1
MLOGIC(QTRIM): %IF condition %qsubstr(&value,&i,1)^=
 is TRUE
MLOGIC(QTRIM): %GOTO trimmed (label resolves to TRIMMED).
SYMBOLGEN: Macro variable I resolves to 1
MLOGIC(QTRIM): %IF condition &i>0 is TRUE
SYMBOLGEN: Macro variable VALUE resolves to 0
SYMBOLGEN: Macro variable I resolves to 1
MLOGIC(QTRIM): Ending execution.
MLOGIC(QLEFT): Parameter TEXT has value 0
MLOGIC(QLEFT): %LOCAL I
SYMBOLGEN: Macro variable TEXT resolves to 0
SYMBOLGEN: Some characters in the above value which were subject to macro quoting have been unquoted for printing.
MLOGIC(QLEFT): %IF condition %length(&text)=0 is FALSE
MLOGIC(QLEFT): %LET (variable name is I)
MLOGIC(VERIFY): Beginning execution.
MLOGIC(VERIFY): This macro was compiled from the autocall file C:\Program Files\SAS\SAS 9.1\core\sasmacro\verify.sas
SYMBOLGEN: Macro variable TEXT resolves to 0
SYMBOLGEN: Some characters in the above value which were subject to macro quoting have been unquoted for printing.
MLOGIC(VERIFY): Parameter TEXT has value 0
MLOGIC(VERIFY): Parameter TARGET has value

MLOGIC(VERIFY): %LOCAL I
SYMBOLGEN: Macro variable TEXT resolves to 0
SYMBOLGEN: Some characters in the above value which were subject to macro quoting have been unquoted for printing.
SYMBOLGEN: Macro variable TARGET resolves to
The SAS System

SYMBOLGEN: Some characters in the above value which were subject to macro quoting have been unquoted for printing.
MLOGIC(VERIFY): %IF condition %length(&text)=0 OR %length(&target)=0 is FALSE
SYMBOLGEN: Macro variable TEXT resolves to 0
SYMBOLGEN: Some characters in the above value which were subject to macro quoting have been unquoted for printing.
MLOGIC(VERIFY): %DO loop beginning; index variable I; start value is 1; stop value is 1; by value is 1.
SYMBOLGEN: Macro variable TARGET resolves to
SYMBOLGEN: Some characters in the above value which were subject to macro quoting have been unquoted for printing.
SYMBOLGEN: Macro variable TEXT resolves to 0
SYMBOLGEN: Some characters in the above value which were subject to macro quoting have been unquoted for printing.
SYMBOLGEN: Macro variable I resolves to 1
MLOGIC(VERIFY): %IF condition NOT %index(&target,%qsubstr(&text,&i,1)) is TRUE
MLOGIC(VERIFY): %GOTO verfnd (label resolves to VERFND).
SYMBOLGEN: Macro variable I resolves to 1
SYMBOLGEN: Macro variable TEXT resolves to 0
SYMBOLGEN: Some characters in the above value which were subject to macro quoting have been unquoted for printing.
MLOGIC(VERIFY): %IF condition &i>%length(&text) is FALSE
SYMBOLGEN: Macro variable I resolves to 1
MLOGIC(VERIFY): Ending execution.
SYMBOLGEN: Macro variable I resolves to 1
MLOGIC(QLEFT): %IF condition &i is TRUE
SYMBOLGEN: Macro variable TEXT resolves to 0
SYMBOLGEN: Some characters in the above value which were subject to macro quoting have been unquoted for printing.
SYMBOLGEN: Macro variable I resolves to 1
MLOGIC(QLEFT): Ending execution.
MLOGIC(QCMPRES): Ending execution.
MLOGIC(STPEND): %IF condition (%qcmpres(&_STPERROR) eq ) is FALSE
SYMBOLGEN: Macro variable _STPERROR resolves to 0
MLOGIC(STPEND): %IF condition (%eval(&_STPERROR) ne 0) is FALSE
MPRINT(STPEND): ods _ALL_ close;
MLOGIC(STPEND): %LET (variable name is QRESULT)
SYMBOLGEN: Macro variable _RESULT resolves to STREAM
SYMBOLGEN: Macro variable QRESULT resolves to STREAM
MLOGIC(STPEND): %IF condition (%length(&QRESULT) lt 7) is TRUE
MLOGIC(STPEND): %LET (variable name is QRESULT)
SYMBOLGEN: Macro variable QRESULT resolves to STREAM
MLOGIC(QCMPRES): Beginning execution.
MLOGIC(QCMPRES): This macro was compiled from the autocall file C:\Program Files\SAS\SAS 9.1\core\sasmacro\qcmpres.sas
SYMBOLGEN: Macro variable QRESULT resolves to STREAMzzzzzzz
The SAS System

MLOGIC(QCMPRES): Parameter TEXT has value STREAMzzzzzzz
MLOGIC(QCMPRES): %LOCAL I
MLOGIC(QCMPRES): %LET (variable name is I)
SYMBOLGEN: Macro variable TEXT resolves to STREAMzzzzzzz
SYMBOLGEN: Macro variable I resolves to 0
MLOGIC(QCMPRES): %DO %WHILE(&i^=0) loop beginning; condition is FALSE. Loop will not be executed.
MLOGIC(QLEFT): Beginning execution.
MLOGIC(QLEFT): This macro was compiled from the autocall file C:\Program Files\SAS\SAS 9.1\core\sasmacro\qleft.sas
MLOGIC(QTRIM): Beginning execution.
MLOGIC(QTRIM): This macro was compiled from the autocall file C:\Program Files\SAS\SAS 9.1\core\sasmacro\qtrim.sas
SYMBOLGEN: Macro variable TEXT resolves to STREAMzzzzzzz
MLOGIC(QTRIM): Parameter VALUE has value STREAMzzzzzzz
MLOGIC(QTRIM): %LOCAL I
SYMBOLGEN: Macro variable VALUE resolves to STREAMzzzzzzz
MLOGIC(QTRIM): %DO loop beginning; index variable I; start value is 13; stop value is 1; by value is -1.
SYMBOLGEN: Macro variable VALUE resolves to STREAMzzzzzzz
SYMBOLGEN: Macro variable I resolves to 13
MLOGIC(QTRIM): %IF condition %qsubstr(&value,&i,1)^=
 is TRUE
MLOGIC(QTRIM): %GOTO trimmed (label resolves to TRIMMED).
SYMBOLGEN: Macro variable I resolves to 13
MLOGIC(QTRIM): %IF condition &i>0 is TRUE
SYMBOLGEN: Macro variable VALUE resolves to STREAMzzzzzzz
SYMBOLGEN: Macro variable I resolves to 13
MLOGIC(QTRIM): Ending execution.
MLOGIC(QLEFT): Parameter TEXT has value STREAMzzzzzzz
MLOGIC(QLEFT): %LOCAL I
SYMBOLGEN: Macro variable TEXT resolves to STREAMzzzzzzz
SYMBOLGEN: Some characters in the above value which were subject to macro quoting have been unquoted for printing.
MLOGIC(QLEFT): %IF condition %length(&text)=0 is FALSE
MLOGIC(QLEFT): %LET (variable name is I)
MLOGIC(VERIFY): Beginning execution.
MLOGIC(VERIFY): This macro was compiled from the autocall file C:\Program Files\SAS\SAS 9.1\core\sasmacro\verify.sas
SYMBOLGEN: Macro variable TEXT resolves to STREAMzzzzzzz
SYMBOLGEN: Some characters in the above value which were subject to macro quoting have been unquoted for printing.
MLOGIC(VERIFY): Parameter TEXT has value STREAMzzzzzzz
MLOGIC(VERIFY): Parameter TARGET has value

MLOGIC(VERIFY): %LOCAL I
SYMBOLGEN: Macro variable TEXT resolves to STREAMzzzzzzz
The SAS System

SYMBOLGEN: Some characters in the above value which were subject to macro quoting have been unquoted for printing.
SYMBOLGEN: Macro variable TARGET resolves to
SYMBOLGEN: Some characters in the above value which were subject to macro quoting have been unquoted for printing.
MLOGIC(VERIFY): %IF condition %length(&text)=0 OR %length(&target)=0 is FALSE
SYMBOLGEN: Macro variable TEXT resolves to STREAMzzzzzzz
SYMBOLGEN: Some characters in the above value which were subject to macro quoting have been unquoted for printing.
MLOGIC(VERIFY): %DO loop beginning; index variable I; start value is 1; stop value is 13; by value is 1.
SYMBOLGEN: Macro variable TARGET resolves to
SYMBOLGEN: Some characters in the above value which were subject to macro quoting have been unquoted for printing.
SYMBOLGEN: Macro variable TEXT resolves to STREAMzzzzzzz
SYMBOLGEN: Some characters in the above value which were subject to macro quoting have been unquoted for printing.
SYMBOLGEN: Macro variable I resolves to 1
MLOGIC(VERIFY): %IF condition NOT %index(&target,%qsubstr(&text,&i,1)) is TRUE
MLOGIC(VERIFY): %GOTO verfnd (label resolves to VERFND).
SYMBOLGEN: Macro variable I resolves to 1
SYMBOLGEN: Macro variable TEXT resolves to STREAMzzzzzzz
SYMBOLGEN: Some characters in the above value which were subject to macro quoting have been unquoted for printing.
MLOGIC(VERIFY): %IF condition &i>%length(&text) is FALSE
SYMBOLGEN: Macro variable I resolves to 1
MLOGIC(VERIFY): Ending execution.
SYMBOLGEN: Macro variable I resolves to 1
MLOGIC(QLEFT): %IF condition &i is TRUE
SYMBOLGEN: Macro variable TEXT resolves to STREAMzzzzzzz
SYMBOLGEN: Some characters in the above value which were subject to macro quoting have been unquoted for printing.
SYMBOLGEN: Macro variable I resolves to 1
MLOGIC(QLEFT): Ending execution.
MLOGIC(QCMPRES): Ending execution.
MLOGIC(STPEND): %IF condition (%qupcase(%qsubstr(%qcmpres(&QRESULT),1,7)) eq PACKAGE) is FALSE
MLOGIC(QCMPRES): Beginning execution.
MLOGIC(QCMPRES): This macro was compiled from the autocall file C:\Program Files\SAS\SAS 9.1\core\sasmacro\qcmpres.sas
SYMBOLGEN: Macro variable _RESULT resolves to STREAM
MLOGIC(QCMPRES): Parameter TEXT has value STREAM
MLOGIC(QCMPRES): %LOCAL I
MLOGIC(QCMPRES): %LET (variable name is I)
SYMBOLGEN: Macro variable TEXT resolves to STREAM
SYMBOLGEN: Macro variable I resolves to 0
MLOGIC(QCMPRES): %DO %WHILE(&i^=0) loop beginning; condition is FALSE. Loop will not be executed.
MLOGIC(QLEFT): Beginning execution.
The SAS System

MLOGIC(QLEFT): This macro was compiled from the autocall file C:\Program Files\SAS\SAS 9.1\core\sasmacro\qleft.sas
MLOGIC(QTRIM): Beginning execution.
MLOGIC(QTRIM): This macro was compiled from the autocall file C:\Program Files\SAS\SAS 9.1\core\sasmacro\qtrim.sas
SYMBOLGEN: Macro variable TEXT resolves to STREAM
MLOGIC(QTRIM): Parameter VALUE has value STREAM
MLOGIC(QTRIM): %LOCAL I
SYMBOLGEN: Macro variable VALUE resolves to STREAM
MLOGIC(QTRIM): %DO loop beginning; index variable I; start value is 6; stop value is 1; by value is -1.
SYMBOLGEN: Macro variable VALUE resolves to STREAM
SYMBOLGEN: Macro variable I resolves to 6
MLOGIC(QTRIM): %IF condition %qsubstr(&value,&i,1)^=
 is TRUE
MLOGIC(QTRIM): %GOTO trimmed (label resolves to TRIMMED).
SYMBOLGEN: Macro variable I resolves to 6
MLOGIC(QTRIM): %IF condition &i>0 is TRUE
SYMBOLGEN: Macro variable VALUE resolves to STREAM
SYMBOLGEN: Macro variable I resolves to 6
MLOGIC(QTRIM): Ending execution.
MLOGIC(QLEFT): Parameter TEXT has value STREAM
MLOGIC(QLEFT): %LOCAL I
SYMBOLGEN: Macro variable TEXT resolves to STREAM
SYMBOLGEN: Some characters in the above value which were subject to macro quoting have been unquoted for printing.
MLOGIC(QLEFT): %IF condition %length(&text)=0 is FALSE
MLOGIC(QLEFT): %LET (variable name is I)
MLOGIC(VERIFY): Beginning execution.
MLOGIC(VERIFY): This macro was compiled from the autocall file C:\Program Files\SAS\SAS 9.1\core\sasmacro\verify.sas
SYMBOLGEN: Macro variable TEXT resolves to STREAM
SYMBOLGEN: Some characters in the above value which were subject to macro quoting have been unquoted for printing.
MLOGIC(VERIFY): Parameter TEXT has value STREAM
MLOGIC(VERIFY): Parameter TARGET has value

MLOGIC(VERIFY): %LOCAL I
SYMBOLGEN: Macro variable TEXT resolves to STREAM
SYMBOLGEN: Some characters in the above value which were subject to macro quoting have been unquoted for printing.
SYMBOLGEN: Macro variable TARGET resolves to
SYMBOLGEN: Some characters in the above value which were subject to macro quoting have been unquoted for printing.
MLOGIC(VERIFY): %IF condition %length(&text)=0 OR %length(&target)=0 is FALSE
SYMBOLGEN: Macro variable TEXT resolves to STREAM
SYMBOLGEN: Some characters in the above value which were subject to macro quoting have been unquoted for printing.
MLOGIC(VERIFY): %DO loop beginning; index variable I; start value is 1; stop value is 6; by value is 1.
The SAS System

SYMBOLGEN: Macro variable TARGET resolves to
SYMBOLGEN: Some characters in the above value which were subject to macro quoting have been unquoted for printing.
SYMBOLGEN: Macro variable TEXT resolves to STREAM
SYMBOLGEN: Some characters in the above value which were subject to macro quoting have been unquoted for printing.
SYMBOLGEN: Macro variable I resolves to 1
MLOGIC(VERIFY): %IF condition NOT %index(&target,%qsubstr(&text,&i,1)) is TRUE
MLOGIC(VERIFY): %GOTO verfnd (label resolves to VERFND).
SYMBOLGEN: Macro variable I resolves to 1
SYMBOLGEN: Macro variable TEXT resolves to STREAM
SYMBOLGEN: Some characters in the above value which were subject to macro quoting have been unquoted for printing.
MLOGIC(VERIFY): %IF condition &i>%length(&text) is FALSE
SYMBOLGEN: Macro variable I resolves to 1
MLOGIC(VERIFY): Ending execution.
SYMBOLGEN: Macro variable I resolves to 1
MLOGIC(QLEFT): %IF condition &i is TRUE
SYMBOLGEN: Macro variable TEXT resolves to STREAM
SYMBOLGEN: Some characters in the above value which were subject to macro quoting have been unquoted for printing.
SYMBOLGEN: Macro variable I resolves to 1
MLOGIC(QLEFT): Ending execution.
MLOGIC(QCMPRES): Ending execution.
MLOGIC(STPEND): %IF condition (%qupcase(%qcmpres(&_RESULT)) eq REPORT_REPOSITORY) is FALSE
SYMBOLGEN: Macro variable SHOWVER resolves to N
SYMBOLGEN: Macro variable SHOWVER resolves to N
MLOGIC(STPEND): %IF condition (%bquote(%upcase(&SHOWVER)) eq Y) or (%bquote(%upcase(&SHOWVER)) eq YES) is FALSE
SYMBOLGEN: Macro variable NOTES resolves to NOTES
MPRINT(STPEND): options NOTES;
MLOGIC(STPEND): Ending execution.
98 +
99 +
100 +/* End EG generated code (do not edit this line) */
NOTE: %INCLUDE (level 1) ending.

[Cynthia_sas]

Sorry, my bad. I did NOT intend for you to turn that code into a stored process. I did not make myself clear. What I really wanted you to do was put the code into a code node (or several code nodes) in EG and just submit and review the log for each step separately.

That code was for "instruction" purposes, only. You have to understand how that code works -- outside the world of stored processes. That code was NOT intended to be a stored process. It was intended to illustrate for you HOW your stored process code might have to be written -- once you understand whether or not to SUPERQ macro variables, how to do comparisons, how to use macro %do loops and %if statements, etc.

At this point, I would recommend that you contact Tech Support. This is as much an analysis and approach problem as a coding problem. Asking "piecework" questions and getting "piecework" answers is really not the best approach in my opinion.

Here's what I recommend:

Development BEFORE making a stored process:
1) have a working SAS program that does what you want for one group, one ethnicity, etc. This program should not use macro variables. You should be able to submit this program from a code node in EG and get the results you want. (this was the code inside the %testdata macro program)

2) add macro variables to this #1 SAS program -- use a few simple %LET statements and &macvar references to make sure you understand where quotes have to be placed in your code to make the &macvar substitution work. Test all possible situations and conditions at this point (for example, no value for a macro parameter vs 2 selections for a macro parameter -- this is the point at which you test your macro logic and macro quoting, etc.) (this was the macro code inside the %testcode macro program)

3) make a macro program out of the #2 program. Be sure that you can invoke this macro program from a code node in EG for every group and that the code works for every group. Once you can do this, you now have a program that you can turn into a stored process.

Development for a Stored Process (finally)
4) Convert the #3 program in the following fashion
4a) remove any hardcoded %LET statements that you used for testing
4b) add a %GLOBAL statement for your macro variables that will be parameters
4c) register the SP in the metadata and define a parameter for every macro variable that you will allow the users
4d) Test the SP in every client application from which it will be invoked.

My test program was a combo of #1 and #2 program -- NOT a stored process -- NOT intended to be a stored process. In my opinion, it is virtually impossible to jump straight to #4 especially if you do not understand SAS macro or SAS programming.

If all you were doing was converting EG tasks and projects to a Stored Process, I'd have a different opinion, but it seems to me that your requirements to convert from a FOCUS system to stored processes, use multiple value selection parameters and manipulate data and macro variable values automatically put you outside the world of "simple". I really recommend that you contact Tech Support for help with this complete program.

I'm sorry I did not make myself clear about what I intended for you to do with the code. In order to "debug" your log at this point, somebody would have to look at your stored process parameters, what was passed from the client application, etc. I believe that there may be some extra quoting issues involved, depending on the server that you're using, but the multiple parameters will only work on the stored process server, not the workspace server -- so Tech Support can help you figure that out too.

cynthia

[deleted_user]

Hi, Cynthia,

Thanks so much for replying...... no doubt I've bitten off more than I should have, but sometimes that's the best way to get "wet," so to speak. I'll review and digest what you've sent me...... I appreciate your replies tremendously.