topic Re: How to avoid infinite loop in my script? in SAS Programming

How to avoid infinite loop in my script?

imdickson — Sun, 23 Sep 2018 16:17:48 GMT

Hi, what my script does is to first scan for xls file in multiple subfolders, then import it and transform it into the target tables according to the source file (1 to 1). It includes transpose and creating new variables(for target table). For now, i havent prepare the script to append all separate tables into 1 big final table.

However, my script been running for 5 hours and is still running. I believe it is in infinite loop. Does anyone notice anything wrong here?

%macro drive(dir,ext);                                                                                                                  
  %local filrf rc did memcnt name i;                                                                                                    
                                                                                                                                        
  /* Assigns a fileref to the directory and opens the directory */                                                           
  %let rc=%sysfunc(filename(filrf,&dir));                                                                                               
  %let did=%sysfunc(dopen(&filrf));                                                                                                     
                                                                                                                                        
  /* Make sure directory can be open */                                                                                                 
  %if &did eq 0 %then %do;                                                                                                              
   %put Directory &dir cannot be open or does not exist;                                                                                
   %return;                                                                                                                             
  %end;                                                                                                                                 
                                                                                                                                        
   /* Loops through entire directory */                                                                                                 
   %do i = 1 %to %sysfunc(dnum(&did));                                                                                                  
                                                                                                                                        
     /* Retrieve name of each file */                                                                                                   
     %let name=%qsysfunc(dread(&did,&i));                                                                                               
                                                                                                                                        
     /* Checks to see if the extension matches the parameter value */                                                                   
     /* If condition is true print the full name to the log        */                                                                   
      %if %qupcase(%qscan(&name,-1,.)) = %upcase(&ext) %then %do; 
        PROC IMPORT OUT=WORK.out&i  DATAFILE= "&dir/&name" 
        /*excelout*/
            DBMS=csv REPLACE;
            delimiter='09'x;
            getnames=no;
        RUN;
        proc contents data=out&i noprint out=data_info /*(keep = name varnum)*/;
        run;

         data _null_;
            set data_info;
            call symputx(compress("col"||VARNUM),compress(NAME));
            call symputx("cnt",_n_);
        run;





        

        data _null_;
        set WORK.out&i (obs=2);
            if _n_ = 2 then do;
            tcnt = 0;
                %do j=1 %to &cnt;
                    if &&col&j not in ("","Total") then do;
                    trxm = &&col&j;
                    call symputx(compress("trxm"||tcnt),compress(trxm));
                    call symputx("tcnt",tcnt);
                    tcnt+1;
                    end;
                %end;
            end;
        run;
         
        %put &trxm0;
        %put &trxm1;
        %put &trxm2;
        %put &tcnt;
         
        data test&i (drop=
        %do k=1 %to &cnt;
        &&col&k..
        %end;
        );
        length station $10 voltage $10 year 8 month $20 transformer $10
        Day $20 Date Time MW_Imp MW_Exp MVAR_Imp MVAR_Exp MVA Power_Factor 8;
        format Time hhmm.;
        set excelout end=last;
        retain station voltage year month;
        if _n_ = 1 then do;
        station = VAR1;
        voltage = VAR2;
        year = input(VAR5,4.);
        month = VAR3;
        end;
        if last then do;
        month = strip(put(intnx('month',input(catt(substr(month,1,3),'1960'),monyy.),1),monname10.));
        if month = "January" then year = year+1;
        end;
         
        if _n_ > 4 then do;
        Day = VAR1;
        Date = VAR2;
        Time = input(VAR3,time.);
        %do m=0 %to &tcnt;
        transformer = "&&trxm&m..";
        MW_Imp = input(VAR%eval(4+%eval(&m*6)),best32.);
        MW_Exp = input(VAR%eval(5+%eval(&m*6)),best32.);
        MVAR_Imp = input(VAR%eval(6+%eval(&m*6)),best32.);
        MVAR_Exp = input(VAR%eval(7+%eval(&m*6)),best32.);
        MVA = input(VAR%eval(8+%eval(&m*6)),best32.);
        if MVA < 0.001 then Power_Factor = 0;
        else Power_Factor = max(MW_Imp,MW_Exp)/MVA;
        output;
        %end;
        end;
        run;

        








        %put &dir/&name;





      %end;                                                                                                                             
     /* If directory name call macro again */                                                                                           
      %else %if %qscan(&name,2,.) = %then %do;                                                                                          
        %drive(&dir/%unquote(&name),&ext)                                                                                               
      %end;                                                                                                                             
                                                                                                                                        
   %end;                                                                                                                                
                                                                                                                                        
  /* Closes the directory and clear the fileref */                                                                                      
  %let rc=%sysfunc(dclose(&did));                                                                                                       
  %let rc=%sysfunc(filename(filrf));                                                                                                    
                                                                                                                                        
%mend drive;                                                                                                                            
                                                                                                                                        
/* First parameter is the directory of where your files are stored. */                                                                  
/* Second parameter is the extension you are looking for.           */                                                                  
%drive(/data/source/tttt/Files,xls)

I suspect the wrong logic starts at this section:

set WORK.out&i (obs=2);
			if _n_ = 2 then do;
			tcnt = 0;
				%do i=1 %to &cnt;
					if &&col&i not in ("","Total") then do;
					trxm = &&col&i;
					call symputx(compress("trxm"||tcnt),compress(trxm));
					call symputx("tcnt",tcnt);
					tcnt+1;
					end;
				%end;
			end;
		run;

This is because the code was prepared in another macro. What i did was to copy the code from another macro into this script. I could be wrong. Please correct me.

I believe my logic may be used incorrectly.

Can anyone spot what was wrong?

Re: How to avoid infinite loop in my script?

imdickson — Sun, 23 Sep 2018 16:18:38 GMT

I've just edited my original code as i copied the wrong script. The current script in my 1st inital above is the 1 that I am currently facing issue.

Re: How to avoid infinite loop in my script?

Shmuel — Sun, 23 Sep 2018 20:06:37 GMT

You have a long code with many steps.

Try use put / %put statement in the do / %do loop to check whether (macro) variables are changing and proceeding correctly.

for example:

%do i=1 %to &n;  /* &n is the highest limit expected */
       %if &i < 4 %then %put I=&i ...
       ....
%end;

You can even run the loop code limited to a small number, so it should end in a short time.

Check the log - you may find what causes the issue.

Re: How to avoid infinite loop in my script?

ballardw — Mon, 24 Sep 2018 15:14:15 GMT

@imdickson wrote:

However, my script been running for 5 hours and is still running. I believe it is in infinite loop. Does anyone notice anything wrong here?

%macro drive(dir,ext);                                                                                                                  
  %local filrf rc did memcnt name i;                                                                                                    
                                                                                                                                        
  /* Assigns a fileref to the directory and opens the directory */                                                           
  %let rc=%sysfunc(filename(filrf,&dir));                                                                                               
  %let did=%sysfunc(dopen(&filrf));                                                                                                     
                                                                                                                                        
  /* Make sure directory can be open */                                                                                                 
  %if &did eq 0 %then %do;                                                                                                              
   %put Directory &dir cannot be open or does not exist;                                                                                
   %return;                                                                                                                             
  %end;                                                                                                                                 
                                                                                                                                        
   /* Loops through entire directory */                                                                                                 
   %do i = 1 %to %sysfunc(dnum(&did));                                                                                                  
                                                                                                                                        
     /* Retrieve name of each file */                                                                                                   
     %let name=%qsysfunc(dread(&did,&i));                                                                                               
                                                                                                                                        
     /* Checks to see if the extension matches the parameter value */                                                                   
     /* If condition is true print the full name to the log        */                                                                   
      %if %qupcase(%qscan(&name,-1,.)) = %upcase(&ext) %then %do; 
        PROC IMPORT OUT=WORK.out&i  DATAFILE= "&dir/&name" 
        /*excelout*/
            DBMS=csv REPLACE;
            delimiter='09'x;
            getnames=no;
        RUN;
        proc contents data=out&i noprint out=data_info /*(keep = name varnum)*/;
        run;

         data _null_;
            set data_info;
            call symputx(compress("col"||VARNUM),compress(NAME));
            call symputx("cnt",_n_);
        run;





        

        data _null_;
        set WORK.out&i (obs=2);
            if _n_ = 2 then do;
            tcnt = 0;
                %do j=1 %to &cnt;
                    if &&col&j not in ("","Total") then do;
                    trxm = &&col&j;
                    call symputx(compress("trxm"||tcnt),compress(trxm));
                    call symputx("tcnt",tcnt);
                    tcnt+1;
                    end;
                %end;
            end;
        run;
         
        %put &trxm0;
        %put &trxm1;
        %put &trxm2;
        %put &tcnt;
         
        data test&i (drop=
        %do k=1 %to &cnt;
        &&col&k..
        %end;
        );
        length station $10 voltage $10 year 8 month $20 transformer $10
        Day $20 Date Time MW_Imp MW_Exp MVAR_Imp MVAR_Exp MVA Power_Factor 8;
        format Time hhmm.;
        set excelout end=last;
        retain station voltage year month;
        if _n_ = 1 then do;
        station = VAR1;
        voltage = VAR2;
        year = input(VAR5,4.);
        month = VAR3;
        end;
        if last then do;
        month = strip(put(intnx('month',input(catt(substr(month,1,3),'1960'),monyy.),1),monname10.));
        if month = "January" then year = year+1;
        end;
         
        if _n_ > 4 then do;
        Day = VAR1;
        Date = VAR2;
        Time = input(VAR3,time.);
        %do m=0 %to &tcnt;
        transformer = "&&trxm&m..";
        MW_Imp = input(VAR%eval(4+%eval(&m*6)),best32.);
        MW_Exp = input(VAR%eval(5+%eval(&m*6)),best32.);
        MVAR_Imp = input(VAR%eval(6+%eval(&m*6)),best32.);
        MVAR_Exp = input(VAR%eval(7+%eval(&m*6)),best32.);
        MVA = input(VAR%eval(8+%eval(&m*6)),best32.);
        if MVA < 0.001 then Power_Factor = 0;
        else Power_Factor = max(MW_Imp,MW_Exp)/MVA;
        output;
        %end;
        end;
        run;

        








        %put &dir/&name;





      %end;                                                                                                                             
     /* If directory name call macro again */                                                                                           
      %else %if %qscan(&name,2,.) = %then %do;                                                                                          
        %drive(&dir/%unquote(&name),&ext)                                                                                               
      %end;                                                                                                                             
                                                                                                                                        
   %end;                                                                                                                                
                                                                                                                                        
  /* Closes the directory and clear the fileref */                                                                                      
  %let rc=%sysfunc(dclose(&did));                                                                                                       
  %let rc=%sysfunc(filename(filrf));                                                                                                    
                                                                                                                                        
%mend drive;                                                                                                                            
                                                                                                                                        
/* First parameter is the directory of where your files are stored. */                                                                  
/* Second parameter is the extension you are looking for.           */                                                                  
%drive(/data/source/tttt/Files,xls)

I suspect the wrong logic starts at this section:

set WORK.out&i (obs=2);
			if _n_ = 2 then do;
			tcnt = 0;
				%do i=1 %to &cnt;
					if &&col&i not in ("","Total") then do;
					trxm = &&col&i;
					call symputx(compress("trxm"||tcnt),compress(trxm));
					call symputx("tcnt",tcnt);
					tcnt+1;
					end;
				%end;
			end;
		run;

This is because the code was prepared in another macro. What i did was to copy the code from another macro into this script. I could be wrong. Please correct me.

I believe my logic may be used incorrectly.

Can anyone spot what was wrong?

Compare the things I have highlighted in RED above and see if you see any inconsistencies - there are a minimum of 2.