Resolving I correctly in a SAS Iterative Do Loop

I'm having problems understanding how the Index variable (i) is applied/resolved inside a do loop. I have a very simple SQL macro-

*Note - The macro is called after a preceding proc sql noprint; as well as the quit; statement outside of the macro.

%macro psql(into,date);
   select count(distinct task) into: &into
   from source
   where c_date between &start and &date;
%mend psql;

The macro is called via - %psql(count10,&date10);

*Note - Macro variables &date10-&date20 have all been previously defined. This works perfectly-the macro var &count10 is created, using the date value from &date10-&date20 for each line calling the %psql macro with its respective parameters.

As always, I have been asked to extend this to many more dates, so listing the %psql macro is going to be repetitive.

Assuming a do loop would be most efficient, I attempted the following in order to use the index(i) to create my variables, instead of many macro calls (each holding new macro parameters):

%macro doloop;
   %do i=10 %to 20;
      proc sql noprint;
         select count(distinct task) into: count&i
         from source
         where c_date between &start and &date&i;
      quit;
   %end;
%mend doloop;

My attempt to use (I) in order to create the &count macro variables and reference the &date parameter does not seem to resolve WITH the actual macro vars being created/referenced, but the varaiable (I) itself seems to resolve correctly during iterations -

SYMBOLGEN:  Macro variable I resolves to 10
SYMBOLGEN:  Macro variable START resolves to    20429
SYMBOLGEN:  Macro variable DATE resolves to    20435
SYMBOLGEN:  Macro variable I resolves to 10

SYMBOLGEN:  Macro variable I resolves to 11
SYMBOLGEN:  Macro variable START resolves to    20429
SYMBOLGEN:  Macro variable DATE resolves to    20435
SYMBOLGEN:  Macro variable I resolves to 11

Does the issue involve my attempting to use (I) in order to "call" an existing macro var, and using (I) to create a macro variable at the same time? How can I reference (I) in order to create my &count variables (&count10-&count....) while using the already existing date variables(&date10-&date....) ?

Solution

I think you are talking about the differences between count&i, &date&i, and &&date&i.

count&i will resolve to COUNT10. And the way your SQL code is written this will be used as the name of the macro variable that receives the count.

&date&i will depend on the value of the DATE macro variable. If the macro variable DATE has the value 20435 then your SQL code will be using the value 2043510, which is in the year 7554, in your date comparison

&&date&i will first reduce to &date10 and then further resolve to the value of the macro variable DATE10. I think this is what you want to use in your SQL code.

Here is an example with SYMBOLGEN turned on.

105  %let date10=SOME DATE VALUE;
106  %let date=ANOTHER DATE VALUE;
107  %let i=10;
108  options symbolgen;
109  %put count&i;
SYMBOLGEN:  Macro variable I resolves to 10
count10
110  %put &date&i;
SYMBOLGEN:  Macro variable DATE resolves to ANOTHER DATE VALUE
SYMBOLGEN:  Macro variable I resolves to 10
ANOTHER DATE VALUE10
111  %put &&date&i;
SYMBOLGEN:  && resolves to &.
SYMBOLGEN:  Macro variable I resolves to 10
SYMBOLGEN:  Macro variable DATE10 resolves to SOME DATE VALUE
SOME DATE VALUE