Local macro problem

[Phil_NZ]

Hi SAS Users,

Today, I ran a macro to run all sheets of an excel file with the same code, leaving an exception for sheet 1 as below:

options compress=yes reuse=yes;

%macro ImportAndTranspose(
      File=
      , StartSheet=
      , EndSheet=
   );
   
   %local i;
   
   %do i = &StartSheet. %to &EndSheet.;

    %if i=1 %then %do;
    proc import datafile= "&File." 
                  out= sheet1
                  dbms= xlsx 
                  replace;
          range= "Sheet1$A:X";
          getnames= yes;
      run;
      
      proc sort data= sheet1;
      by Type;
      run;
	%end;

    %else %if i ne 1 %then %do;
      proc import datafile= "&File." 
                  out= sheet&i.
                  dbms= xlsx 
                  replace;
          range= "Sheet&i.$A:AG";
          getnames= yes;
      run;
      
      proc sort data= sheet&i.;
      by Type;
      run;

      proc transpose data= sheet&i. 
            out= sheet&i._out(rename=(COL1=s&i. _NAME_=Year) drop=_label_);
         by Type;
         VAR '1988'N - '2019'N;
      run;
	  proc print data=sheet&i._out;
	  run;

      DATA sheet&i._outx;
      set sheet&i._out;

      if s&i. not in: ('NA', '$$') then s&i.2=input(s&i., 32.);
	  drop s&i.;
      run;
	  %end;

   %end;
   
%mend;

%ImportAndTranspose(
      File= C:\Users\pnguyen\Desktop\Argentina_.xlsx
      , StartSheet= 1
      , EndSheet= 4);

However, the result turns out that SAS even read the column from A to AG in sheet1 and result in sheet1_outx as well while the code for sheet 1 is only

%if i=1 %then %do;
    proc import datafile= "&File." 
                  out= sheet1
                  dbms= xlsx 
                  replace;
          range= "Sheet1$A:X";
          getnames= yes;
      run;
      
      proc sort data= sheet1;
      by Type;
      run;
	%end;

I am wondering if there is any point that I did wrong in the code.

Many thanks and warmest regards.

[RichardDeVen · Accepted solution]

You need to use ampersand (&) to properly resolve your macro %do loop index variable i in your macro test expression

Change

%if i=1 %then %do;

to

%if &i=1 %then %do;   /* all hail the important ampersand */

What happened?

Because the letter i is never literally 1 your first expected %then %do block did not execute

Because the letter i is always never literally 1 the second %then %do block was executed for each sheet.