SAS macro with conditional loops (%if %then %else %do %end)

Question

I'm a beginner in SAS and I am trying to use a macro to import excel files using a conditional loop. The importing process is based on initial_year ; final_year; initial_month and final_month values. But it seems the If condition is not working. Can you help please. Thank you.

This is my sas program:

%let path=\\xxxx.yy.pt\aaa$\INFO\; 
%let initial_year=2019; %let initial_month=2;  
%let final_year=2021; %let final_month=1;

%Macro import_loop; 

 %if &final_month >= &initial_month %then %do;

    %DO x = &initial_year %TO &final_year;

        %DO i = &initial_month %TO &final_month;

            %if &i <=9 %then %let anomes=&x.0&i;
            %else %let anomes=&x&i ;

            proc import datafile="&path&x\Farmacias_EA_&anomes..xlsx" 
            out=Farmacias_EA_&anomes REPLACE dbms=xlsx;
            run;

            data Farmacias_EA_&anomes;
            set Farmacias_EA_&anomes;
            Data_anomes=&anomes;
            run;
 
        %end;
    %end;

%else %do
 

     %DO x = &initial_year %TO &final_year-1;

        %DO i = &initial_month %TO 12;

            %if &i <=9 %then %let anomes=&x.0&i;
            %else %let anomes=&x&i ;

            proc import datafile="&path&x\Farmacias_EA_&anomes..xlsx" 
            out=Farmacias_EA_&anomes REPLACE dbms=xlsx;
            run;

            data Farmacias_EA_&anomes;
            set Farmacias_EA_&anomes;
            Data_anomes=&anomes;
            run;
 
        %end;
    %end;

        %DO x = &final_year %TO &final_year;

        %DO i = 1 %TO &final_month;

            %if &i <=9 %then %let anomes=&x.0&i;
            %else %let anomes=&x&i ;

            proc import datafile="&path&x\Farmacias_EA_&anomes..xlsx" 
            out=Farmacias_EA_&anomes REPLACE dbms=xlsx;
            run;

            data Farmacias_EA_&anomes;
            set Farmacias_EA_&anomes;
            Data_anomes=&anomes;
            run;
 
        %end;
    %end;

%end;

%mend import_loop;
%import_loop

Tom Tom · Accepted Answer · 2021-03-14T17:26:42

Just treat your dates like dates and the looping will be much easier.

%macro import(from,to);
%local start end offset yymm year ;
%let start=%sysfunc(inputn(&from.01,yymmdd8.));
%let end=%sysfunc(inputn(&to.01,yymmdd8.));
%do offset=0 %to %sysfunc(intck(month,&start,&end));
  %let yymm=%sysfunc(intnx(month,&start,&offset),yymmn6.);
  %let year=%substr(&yymm,1,4);

proc import datafile="&path.&year.\Farmacias_EA_&yymm..xlsx" 
  out=Farmacias_EA_&yymm. REPLACE dbms=xlsx
;
run;

data Farmacias_EA_&yymm.;
  set Farmacias_EA_&yymm.;
  Data_anomes=&yymm.;
run;

%end;
%mend import ;


%let path=\\xxxx.yy.pt\aaa$\INFO\; 
%import(201902,201201);

Do you really want the variable DATA_ANOMES to have numbers like 201,902 ? Why not either store it as a string like "201902" or an actual date value like "01FEB2019"d ?

SAS macro with conditional loops (%if %then %else %do %end)

1 Answers