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?
Click image to register for webinar
