MACRO Loop error with SAS EG Prompt

[GriffT]

I am having difficulties wth a macro loop that derives input from a prompt value. Below are my configurations:

SAS Enterprise Guide 7.1

Prompt:

• Prompt Type: Text
• Method for populating prompt: User enters values
• Number of values: Multiple values
• No minimums, maximums or default values entered
• Prompt value is used throughout the project

The issue is that the code will fail if only one value is supplied to the prompt, if two or more are supplied the macro processes clean and produces the expected results. Additionally if you run the program with a single prompt value, run again with multiple values and then run again with a single prompt value the macro processes and produces the expected results. I am guessing that since I am not clearing the macro variables between runs this is why there are no errors in this instance. I need to be albe to execute this macro with one or more prompts supplied by the end user.

Below I have included an example of the macro loop I am attempting to create  using the SASHELP.BASEBALL dataset and I have also included a log file export with the error message.

options symbolgen mlogic mfile ;

%macro LOOP_ia ;
	%do i = 1 %to &DIV_COUNT. ;
		proc sql ;
		create table DIV_&&DIV&i. as
			select	Name
						,Team
						,Division
						,Div
						,League
						,Position
			from		SASHELP.BASEBALL
			where		Division = propcase("&&DIV&i.")
			order by	Team, Name, Position
			;
		quit ;
	%put &DIV. ;
	%end ;
%mend LOOP_ia ;

%LOOP_ia ;

This an excerpt from the attached log file export.

MLOGIC(LOOP_IA):  Beginning execution.
SYMBOLGEN:  Macro variable DIV_COUNT resolves to 1
MLOGIC(LOOP_IA):  %DO loop beginning; index variable I; start value is 1; stop value is 1; by value is 1.  
SYMBOLGEN:  && resolves to &.
SYMBOLGEN:  Macro variable I resolves to 1
NOTE: Line generated by the macro variable "I".
48          DIV_&DIV1
               _
                22
                200
WARNING: Apparent symbolic reference DIV1 not resolved.
SYMBOLGEN:  && resolves to &.
SYMBOLGEN:  Macro variable I resolves to 1
WARNING: Apparent symbolic reference DIV1 not resolved.
ERROR 22-322: Syntax error, expecting one of the following: (, '.', AS, LIKE.  

ERROR 200-322: The symbol is not recognized and will be ignored.

[CliftonDenning]

It looks like the issue is related to your macro variable call

DIV_&&DIV&i.

SAS thinks you mean DIV1, DIV2, etc. 

If you use DIV_&&DIV.&i. that should fix the issue. 

[GriffT]

That change produces a dataset with no entries and the logfile shows this:

MLOGIC(LOOP_IA):  Beginning execution.
SYMBOLGEN:  Macro variable DIV_COUNT resolves to 1
MLOGIC(LOOP_IA):  %DO loop beginning; index variable I; start value is 1; stop value is 1; by value is 1.  
SYMBOLGEN:  && resolves to &.
SYMBOLGEN:  Macro variable I resolves to 1
SYMBOLGEN:  Macro variable DIV resolves to West
SYMBOLGEN:  && resolves to &.
SYMBOLGEN:  Macro variable I resolves to 1
WARNING: Apparent symbolic reference DIV1 not resolved.
NOTE: Table WORK.DIV_WEST1 created, with 0 rows and 6 columns.

[Astounding]

When the user enters multiple responses to the DIV prompt, are they automatically named DIV1, DIV2, DIV3, or something else (such as DIV, DIV2, DIV3)?

[GriffT]

Yes there are system generated GLOBAL MACRO variables for each pormpt value entered including one for a count of the number of prompt values entered.

[Astounding]

Just to doublecheck ... have  you confirmed that the name of the first value entered is always assigned to DIV1 (not DIV)?

[GriffT]

This is where the problem is I suspect. Executed the program with one and with two propmpt values and after each run I rna the code below.

%put _all_ ;

Which yielded the log results below.

RUN 1 - Single Prompt Value
25         GOPTIONS ACCESSIBLE;
26         %put _all_ ;
GLOBAL DIV West
GLOBAL DIV_COUNT 1

RUN 2 - Multiple Prompt Values
28         GOPTIONS ACCESSIBLE;
29         %put _all_ ;
GLOBAL DIV West
GLOBAL DIV0 2
GLOBAL DIV1 West
GLOBAL DIV2 East
GLOBAL DIV_COUNT 2

Using DIV_&&DIV&i. to create the table when there is only a single prompt value supplied changes the system generated GLOBAL macro variables. So the question becomes how do I overcome this?

[Astounding]

It gets a little messy, but it's not too bad since you have the count available.  Here's one way.

What's there now:

create table DIV_&&DIV&i. as

What it becomes:

create table

%if &div_count=1 %then DIV_DIV;

%else &&DIV&i;

as

Similarly, what you have now:

propcase("&&DIV&i.")

What it becomes:

propcase("

%if &div_count=1 %then DIV;

%else &&DIV&i;

")

[GriffT]

I was unable to get this solution to work, this could be and may very well be a user (ME) error.

[Tom · Accepted solution]

To make the macro variables more consistent between the case when 1 choice is made versus more than one you can just add this code make which will make sure that DIV1 is always there.  You can also make sure DIV0 is created if you want to use that instead of DIV_COUNT.

%let DIV1=÷
%let DIV0=&DIV_COUNT;

But you might need to check what happens if there are none selected. In that case is DIV created with an empty value?

[GriffT]

Thank you sir! This worked brilliantly and seems to have solved my problem. Using the SASHELP.BASEBALL dataset and "East" and "West" as the inputs for the Division field and prompt here is what I found.

- if a SINGLE prompt value is entered
  GLOBAL DIV East
  GLOBAL DIV_COUNT 1

- if MULTIPLE prompt values are entered
  GLOBAL DIV East
  GLOBAL DIV0 2
  GLOBAL DIV1 East
  GLOBAL DIV2 West
  GLOBAL DIV_COUNT 2

The DIV GLOBAL macro variable is constant regardless of SINGLE or MULTIPLE prompt values entered and it
corresponds to the FIRST value entered into the prompt.