Macro Not Properly Importing Files, Variable Misinterpretation

[ImASasMan]

Good afternoon,

The code below is meant to check to see if the file that is it to be pulled from exists and then pulls specific sheets from the file depending upon the variable END_MONTH. Normally the variable END_Month is calculated, but I wanted to try and hard code it to see if it would fix my issue, but unfortunately it did not. The output from the code is one Sheet titled RS_2020_Q1 and ALL_2020_RS. This leads me to believe that the variable END_Month is being misinterpreted. Any assistance you can offer is greatly appreciated.

%LET RISK_PATH = \\leto\sas\Finance\[Omitted]\Risk Scores\;

%LET RSY4 = &YEAR4. Risk Scores;

%LET RISK_CHECK = &RISK_PATH.&RSY4..XLSX;

FILENAME Y4RS  "&RISK_PATH.&RSY4.";

%macro ImportY4;

%LET END_MONTH = 6;

%if %sysfunc(fileexist(&RISK_CHECK.)) ge 1 %then %do;

     %if &END_MONTH. =< 3 %then %do;

           Proc Import out= WORK.RS_&Year4._Q1 DATAFILE= Y4RS DBMS=xlsx REPLACE;

           SHEET="&Year4. Q1";

           GETNAMES=YES;

           RUN;

           DATA ALL_&YEAR4._RS;

           SET WORK.RS_&Year4._Q1;

           RUN;

     %END;

     %else %if 3 < &END_MONTH. =<6 %then %do;

           %do i = 1 %to 2;

                Proc Import out= WORK.RS_&Year4._Q&i DATAFILE= Y4RS DBMS=xlsx REPLACE;

                SHEET="&Year4. Q&i";

                GETNAMES=YES;

                RUN;

           %end;

           DATA ALL_&YEAR4._RS;

           SET WORK.RS_&Year4._Q1 WORK.RS_&Year4._Q2;

           RUN;

     %END;

     %else %if 6 < &END_MONTH.  =<9 %then %do;

           %do i = 1 %to 3;

                Proc Import out= WORK.RS_&Year4._Q&i DATAFILE= Y4RS DBMS=xlsx REPLACE;

                SHEET="&Year4. Q&i";

                GETNAMES=YES;

                RUN;

           %end;

           DATA ALL_&YEAR4._RS;

           SET WORK.RS_&Year4._Q1 WORK.RS_&Year4._Q2 WORK.RS_&Year4._Q3;

           RUN;

     %END;

     %else %if 9 < &END_MONTH.  =<12 %then %do;

           %do i = 1 %to 4;

                Proc Import out= WORK.RS_&Year4._Q&i DATAFILE= Y4RS DBMS=xlsx REPLACE;

                SHEET="&Year4. Q&i";

                GETNAMES=YES;

                RUN;

           %end;

           DATA ALL_&YEAR4._RS;

           SET WORK.RS_&Year4._Q1 WORK.RS_&Year4._Q2 WORK.RS_&Year4._Q3 WORK.RS_&Year4._Q4;

           RUN;

     %END;

%END;

%else %put

***************************

The file &RISK_CHECK. does not exist

****************************;

%MEND ImportY4;

%ImportY4;

[s_lassen]

You have two errors in your macro. The first is that you use "=<" instead of "<=" for "less than or equal to".

The other is constructs like "%if 3< &x <=6" (I corrected the first error here) which the macro language does not interpret the same way as the data step language - instead it just starts from left and evaluates. 

Example (from log):

 73         %put %eval(1<5<3);
 1

Why does that evaluate to 1, when 5 is not between 1 and 3? Because it is evaluated like "(1<5)<3". 1<5 returns the value 1 (true), and then "1<3" is evaluated, returning 1.

In macro language you have to use AND instead.

Or, in your case, just skip the first part of the evaluation, as we are in the %ELSE from the previous %IF.

So change "%else %if 3 < &END_MONTH. =<6 %then %do;" to "%else %if &END_MONTH. <=6 %then %do;", and so on.

[Tom]

As already pointed out the macro processor does not understand the A<B<C syntax the way that data step and where clauses will.  But you don't need to have such a complicated macro.  Looks like you just want to read sheets for all quarters up to and including the one for the month.  So just calculate the final quarter number from the month number.

  %let lastq = %eval((&end_month-1)/3+1);

Then you can just use one %DO loop to read the sheets.

  %do q = 1 %to &lastq;
Proc Import out= WORK.RS_&Year4._Q&q. DATAFILE= Y4RS DBMS=xlsx REPLACE;
  SHEET="&Year4. Q&q.";
  GETNAMES=YES;
RUN;
  %end;

And a simple dataset list in the SET statement.

DATA ALL_&YEAR4._RS;
  SET WORK.RS_&Year4._Q1-WORK.RS_&Year4._Q&lastq.;
RUN;