@Tom this is the script that i mentioned in another thread before but this time round is another issue.
This script will first read 30k of seperate csv files in multiple subfolders that will be scanned by recursive macro.
Once it is read, it will then do some checking on the rows of excel first. For example, If 1st row then assign 1st column to a Variable.
If 2nd row, scan for any value that is not BLANK or TOTAL. However, after running the previous script for a few rounds, i noticed there are errors caused by some CSV that are not having any other value apart from BLANK or TOTAL in 2nd row. This is actually the type of CSV that i should be avoiding to load. Since I've assigned counter for 2nd row and if it has nothing(Apart from BLANK or TOTAL), the counter will not be initialized. I then use symexists to check if the counter for 2nd row(tcnt) exist or not. If it exists, proceed with all other formula calculation for each column and etc. Otherwise, i want to output the filename that tcnt doesnt exists into a table for a full list of bad csv file and i've also included "CONTINUE" at the end of the else statement to skip the current iteration.
However, when i make the script to scan for jsut 3 files, it will output the bad csv into a table called blankfilelist. When i make the script to scan for 1000 files which contains 1 or 2 bad csv file, it will never hit the condition of symexists else(which indicates tcnt doesnt exist).
DATA FINALTESTONE;
STOP;
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;
RUN;
data blankfilelist;
stop;
length blankfilename $50.;
run;
options nomprint nomlogic nosymbolgen;
%macro list_files(dir,ext);
%local filrf rc did memcnt name i;
%let rc=%sysfunc(filename(filrf,&dir));
%let did=%sysfunc(dopen(&filrf));
%if &did eq 0 %then
%do;
%put Directory &dir cannot be open or does not exist;
%return;
%end;
%do i = 1 %to %sysfunc(dnum(&did));
%let name=%qsysfunc(dread(&did,&i));
%if %qupcase(%qscan(&name,-1,.)) = %upcase(&ext) %then
%do;
%put &dir/&name;
%let file_name = %qscan(&name,1,.);
%put &file_name;
/*Alfred added this*/
PROC IMPORT OUT=WORK.out/*&i%substr(&name,7,4)*/ DATAFILE= "&dir/&name"
/*excelout*/
DBMS=csv REPLACE;
GUESSINGROWS=3000;
delimiter='09'x;
getnames=no;
RUN;
proc contents data=out/*&i&name*/ 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;
/*Alfred test cutting macro into this portion*/
data _null_;
set WORK.out/*&i%substr(&name,7,4)*/ (obs=2);
if _n_ = 2 then do;
tcnt = 0;
%do j=1 %to &cnt;
if &&col&j not in ("","Total") /*and (&&col&j in ("T1") or &&col&j in ("T3"))*/ /*Alfred add on the right*/ then do;
trxm = &&col&j;
call symputx(compress("trxm"||tcnt),compress(trxm));
call symputx("tcnt",tcnt);
tcnt+1;
end;
/* end;*/
%end;
end;
run;
/*data checkcnt;*/
/*set work.out;*/
/*length blankfilename $50; */
%if %symexist(tcnt) %then %do;
%put tcnt ada;
/*Alfred added code below*/
data test/*&i%substr(&name,7,4)*/ (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
out/*&i%substr(&name,7,4)*/ /*out4 out5 out6 out7 out8*/ end=last;
/* %do z = 1 %to %sysfunc(dnum(&did)); */
/* out&z. end=last;*/
/* %end;*/
/* by VAR1 VAR2 VAR3 VAR4 VAR5; */
retain station voltage year month;
/* Check if row 2 is empty or not. If empty then skip*/
/* if &tcnt eq . then do;*/
/* %put empty csv name is &name;*/
/* data blankcsv;*/
/* proc append base=blankcsv data=&*/
/* end;*/
/* if &tcnt ne . then do; */
if _n_ = 1 /*&tcnt ne 0*/ then do;
station = VAR1;
voltage = VAR2;
year = input(VAR5,4.);
month = VAR3;
end;
if last /*&tcnt ne 0*/ 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 /*&tcnt ne 0*/ 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;
/* end; */
run;
/* Alfred added code below*/
/* %do y=2 %to %sysfunc(dnum(&did));*/
/* data finaltest;*/
/* set test;*/
/* run;*/
proc append base=finaltestone data=test FORCE;
run;
/* data finaltest;*/
/* run; */
/* proc sql; */
/* select * from finaltest; */
/* quit; */
/* %end; */
/*End of alfred test*/
/* data _tmp; */
/* length dir $512 name $100;*/
/* dir=symget("dir");*/
/* name=symget("name");*/
/* path = catx('/',dir,name);*/
/* the_name = substr(name,1,find(name,'.')-1);*/
/* run; */
/* proc append base=list data=_tmp force; */
/* run; */
/* */
/* quit; */
/* proc sql; */
/* drop table _tmp;*/
/* quit; */
%end;
%else %do;
%put tcnt hilang;
data checkcnt;
/*set work.out;*/
length blankfilename $50;
blankfilename = "&name";
/*SYMGET('file_name');*/
run;
proc append base=blankfilelist data=checkcnt force;
run;
%LET jump=1;
%if jump =1 %then %do; CONTINUE;
%end;
/* output;*/
%end;
%end;
%else %if %qscan(&name,2,.) = %then
%do;
%list_files(&dir/&name,&ext)
%end;
%end;
%let rc=%sysfunc(dclose(&did));
%let rc=%sysfunc(filename(filrf));
%mend list_files;
%list_files(/sasdata/source/tnbt/BIGTEST,xls);
Here i attached the sample code.
Tom, can you tell me more about "place them on RAM disk" ?
Click image to register for webinar
