topic Re: Macro collides after a numbers of loops MW_Imp = input(VAR%eval(4+%eval(&m*6)),best32.); in SAS Programming

Macro collides after a numbers of loops

imdickson — Wed, 26 Sep 2018 02:49:01 GMT

Hi,

First of all, let me explain the situation now. I have a script that has macro to read from csv files, and then process with some calculation for every cells. The script is written to read all subfolders recursively.

The problem is, when I run the script to scan all subfolders, it will generate errors after looping multiple times. When i say looping multiple times, i mean it could be more than a thousand times because i have 30k csv files in the main folder which resides in multiple subfolders. Here is the thing, the log shows :

WARNING: Apparent symbolic reference TCNT not resolved.
ERROR: A character operand was found in the %EVAL function or %IF condition where a numeric operand is required. The condition was: &tcnt
ERROR: The %TO value of the %DO M loop is invalid.
ERROR: The macro LIST_FILES will stop executing.
/sasdata/source/tttt/Profile_List_Files/Central/MDAM/Central_MDAM_33_201506.csv
Central_MDAM_33_201506

As we can see, it starts to throw error when this file is being processed. What i did was to change the script to only read and process this particular file, Central_MDAM_33_201506.csv, instead of scanning the entire directory. No issue at all. No warning no error and no (NOTE: Invalid XXX) at all.

I then tried to make it scan just 1k files including Central_MDAM_33_201506.csv. No issue at all. No warning no error and no (NOTE: Invalid XXX) at all.

HOWEVER, when i run the script to scan through the main folder that consists of 30k files including Central_MDAM_33_201506.csv, it starts throwing error again at this file and remain the same for the remaining files in the folder(shows in the log).

After further research online, i believe this is the case of "Macro collide" where i have to add

%local <variable variable> (such as i j k cnt that i used in the loop within macro)

and add code below after %mend

%sysmstoreclear;
%symdel <variable variable>;

However, the same error appear even after the approach mentioned. Now I am cracking my head with my team and still unable to figure out which part is wrong.

Do let me know if you want to see the script but the script is very long and previously there were experts like Tom and etc advised me not to use the proc import....... But feel free to ask me if you need the script, i will then post it.

I have attached the small part of the code that is within the macro in the post below.

But to wrap up, there wont be any error warning or invalid note if i were to just import 1 to 1000 csv files but ERROR(as above) when i let it scan 30k files.

Re: Macro collides after a numbers of loops

imdickson — Wed, 26 Sep 2018 02:57:36 GMT

I have decided to post a small portion of the codes that is within the macro and containts tcnt which is in the log files.

data _null_; 
 set WORK.out (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; 

data test (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  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;

Kindly refer to code above.

As I've said, no issue if i jsut scan for 1000 files....but when i let it scan for all files, error occur.

Re: Macro collides after a numbers of loops

ChrisNZ — Wed, 26 Sep 2018 02:59:33 GMT

1. It is impossible to know why the macro variable TCNT is not created without seeing the code.

2. You should always ensure the scope of your macro variables is perfectly explicit, i.e. use %local as much as needed.

3. This error message does not seem to be linked to an excess of recursions (the macro calling ifself).

SAS can handle quite a few recursive calls depending on the task performed. This takes 5 seconds to run:

%let a=1;
%macro t; 
  %let a=%eval(&a+1); 
  %put &=a; 
  %if &a<1000 %then %t;
%mend; 
%t

4. If your code is long, I suggest you take out as much as you can (start by just running the loops with no inner processing) and slowly remove more and more code until the error goes away. You'll then know what causes it. Options MLOGIC and SYMBOLGEN can help see what the issue is.

Re: Macro collides after a numbers of loops MW_Imp = input(VAR%eval(4+%eval(&m*6)),best32.);

ChrisNZ — Wed, 26 Sep 2018 03:07:45 GMT

Following your update:

- From the code you posted, I see no reason why TNCT would suddenly no longer exist.

- Try to pinpoint what line of code triggered the error message.

- MW_Imp = input(VAR%eval(4+%eval(&m*6)),best32.);

can be written as

MW_Imp = input(VAR%eval(4+&m*6),best32.);

Re: Macro collides after a numbers of loops MW_Imp = input(VAR%eval(4+%eval(&m*6)),best32.);

imdickson — Wed, 26 Sep 2018 03:20:29 GMT

@ChrisNZ This is the problem. I see no reason why tcnt would lost count suddenly.

I did put mprint mlogic previously but not sure if the log file is still being kept or removed. Let me try to search through the PC for it. I will post it later if i found it.

Re: Macro collides after a numbers of loops MW_Imp = input(VAR%eval(4+%eval(&m*6)),best32.);

imdickson — Wed, 26 Sep 2018 03:23:31 GMT

@ChrisNZ

below is the log when i enable mprint mlogic symbolgen

MPRINT(LIST_FILES): data _null_;
MPRINT(LIST_FILES): set WORK.out (obs=2);
MPRINT(LIST_FILES): if _n_ = 2 then do;
MPRINT(LIST_FILES): tcnt = 0;
MPRINT(LIST_FILES): if VAR1 not in ("","Total") then do;
MPRINT(LIST_FILES): trxm = VAR1;
MPRINT(LIST_FILES): call symputx(compress("trxm"||tcnt),compress(trxm));
MPRINT(LIST_FILES): call symputx("tcnt",tcnt);
MPRINT(LIST_FILES): tcnt+1;
MPRINT(LIST_FILES): end;
MPRINT(LIST_FILES): if VAR2 not in ("","Total") then do;
MPRINT(LIST_FILES): trxm = VAR2;
MPRINT(LIST_FILES): call symputx(compress("trxm"||tcnt),compress(trxm));
MPRINT(LIST_FILES): call symputx("tcnt",tcnt);
MPRINT(LIST_FILES): tcnt+1;
MPRINT(LIST_FILES): end;
MPRINT(LIST_FILES): if VAR3 not in ("","Total") then do;
MPRINT(LIST_FILES): trxm = VAR3;
MPRINT(LIST_FILES): call symputx(compress("trxm"||tcnt),compress(trxm));
MPRINT(LIST_FILES): call symputx("tcnt",tcnt);
MPRINT(LIST_FILES): tcnt+1;
MPRINT(LIST_FILES): end;
MPRINT(LIST_FILES): if VAR4 not in ("","Total") then do;
MPRINT(LIST_FILES): trxm = VAR4;
MPRINT(LIST_FILES): call symputx(compress("trxm"||tcnt),compress(trxm));
MPRINT(LIST_FILES): call symputx("tcnt",tcnt);
MPRINT(LIST_FILES): tcnt+1;
MPRINT(LIST_FILES): end;
MPRINT(LIST_FILES): if VAR5 not in ("","Total") then do;
MPRINT(LIST_FILES): trxm = VAR5;
MPRINT(LIST_FILES): call symputx(compress("trxm"||tcnt),compress(trxm));
MPRINT(LIST_FILES): call symputx("tcnt",tcnt);
MPRINT(LIST_FILES): tcnt+1;
MPRINT(LIST_FILES): end;
MPRINT(LIST_FILES): if VAR6 not in ("","Total") then do;
MPRINT(LIST_FILES): trxm = VAR6;
MPRINT(LIST_FILES): call symputx(compress("trxm"||tcnt),compress(trxm));
MPRINT(LIST_FILES): call symputx("tcnt",tcnt);
MPRINT(LIST_FILES): tcnt+1;
MPRINT(LIST_FILES): end;
MPRINT(LIST_FILES): if VAR7 not in ("","Total") then do;
MPRINT(LIST_FILES): trxm = VAR7;
MPRINT(LIST_FILES): call symputx(compress("trxm"||tcnt),compress(trxm));
MPRINT(LIST_FILES): call symputx("tcnt",tcnt);
MPRINT(LIST_FILES): tcnt+1;
MPRINT(LIST_FILES): end;
MPRINT(LIST_FILES): if VAR8 not in ("","Total") then do;
MPRINT(LIST_FILES): trxm = VAR8;
MPRINT(LIST_FILES): call symputx(compress("trxm"||tcnt),compress(trxm));
MPRINT(LIST_FILES): call symputx("tcnt",tcnt);
MPRINT(LIST_FILES): tcnt+1;
MPRINT(LIST_FILES): end;
MPRINT(LIST_FILES): if VAR9 not in ("","Total") then do;
MPRINT(LIST_FILES): trxm = VAR9;
MPRINT(LIST_FILES): call symputx(compress("trxm"||tcnt),compress(trxm));
MPRINT(LIST_FILES): call symputx("tcnt",tcnt);
MPRINT(LIST_FILES): tcnt+1;
MPRINT(LIST_FILES): end;
MPRINT(LIST_FILES): if VAR10 not in ("","Total") then do;
MPRINT(LIST_FILES): trxm = VAR10;
MPRINT(LIST_FILES): call symputx(compress("trxm"||tcnt),compress(trxm));
MPRINT(LIST_FILES): call symputx("tcnt",tcnt);
MPRINT(LIST_FILES): tcnt+1;
MPRINT(LIST_FILES): end;
MPRINT(LIST_FILES): end;
MPRINT(LIST_FILES): run;
NOTE: Numeric values have been converted to character values at the places given by: (Line):(Column).
366618:104 366618:104 366618:104 366618:104 366618:104 366618:104 366618:104 366618:104 366618:104 366618:104
NOTE: There were 2 observations read from the data set WORK.OUT.
NOTE: DATA statement used (Total process time):
real time 0.00 seconds
cpu time 0.01 seconds

MPRINT(LIST_FILES): data test (drop= VAR1 VAR2 VAR3 VAR4 VAR5 VAR6 VAR7 VAR8 VAR9 VAR10 );
MPRINT(LIST_FILES): 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;
MPRINT(LIST_FILES): format Time hhmm.;
MPRINT(LIST_FILES): set out end=last;
MPRINT(LIST_FILES): retain station voltage year month;
MPRINT(LIST_FILES): if _n_ = 1 then do;
MPRINT(LIST_FILES): station = VAR1;
MPRINT(LIST_FILES): voltage = VAR2;
MPRINT(LIST_FILES): year = input(VAR5,4.);
MPRINT(LIST_FILES): month = VAR3;
MPRINT(LIST_FILES): end;
MPRINT(LIST_FILES): if last then do;
MPRINT(LIST_FILES): month = strip(put(intnx('month',input(catt(substr(month,1,3),'1960'),monyy.),1),monname10.));
MPRINT(LIST_FILES): if month = "January" then year = year+1;
MPRINT(LIST_FILES): end;
MPRINT(LIST_FILES): if _n_ > 4 then do;
MPRINT(LIST_FILES): Day = VAR1;
MPRINT(LIST_FILES): Date = VAR2;
MPRINT(LIST_FILES): Time = input(VAR3,time.);
WARNING: Apparent symbolic reference TCNT not resolved.
ERROR: A character operand was found in the %EVAL function or %IF condition where a numeric operand is required. The condition was: &tcnt
ERROR: The %TO value of the %DO M loop is invalid.
ERROR: The macro LIST_FILES will stop executing.
/sasdata/source/tttt/Profile_List_Files/Central/MDAM/Central_MDAM_33_201506.xls
Central_MDAM_33_201506
NOTE: Character values have been converted to numeric values at the places given by: (Line):(Column).
366618:119
NOTE: The SAS System stopped processing this step because of errors.
WARNING: The data set WORK.TEST may be incomplete. When this step was stopped there were 0 observations and 14 variables.
WARNING: Data set WORK.TEST was not replaced because this step was stopped.
NOTE: DATA statement used (Total process time):
real time 0.00 seconds
cpu time 0.01 seconds

NOTE: Line generated by the invoked macro "LIST_FILES".
366618 PROC IMPORT OUT=WORK.out DATAFILE= "&dir/&name" DBMS=csv REPLACE; delimiter='09'x; getnames=no; RUN; proc contents data=out noprint out=data_info ; run; data _null_; set
_
117
MPRINT(LIST_FILES): PROC IMPORT OUT=WORK.out DATAFILE= "/sasdata/source/tttt/Profile_List_Files/Central/MDAM/Central_MDAM_33_201506.xls" DBMS=csv REPLACE;
ERROR 117-185: There was 1 unclosed DO block.

Re: Macro collides after a numbers of loops MW_Imp = input(VAR%eval(4+%eval(&m*6)),best32.);

ChrisNZ — Wed, 26 Sep 2018 03:49:32 GMT

Following the log snippet you included :

1. Remove the data step error: ERROR 117-185: There was 1 unclosed DO block.

2. The data step will never call symputx() when TNCT=0. This could cause the issue you reported.

3. Strive to have a clean log and remove NOTE: Numeric values have been converted to character values

Re: Macro collides after a numbers of loops MW_Imp = input(VAR%eval(4+%eval(&m*6)),best32.);

imdickson — Wed, 26 Sep 2018 03:52:37 GMT

@ChrisNZ you mean you want the above log by removing the 3 items from the log that you just listed out?

Re: Macro collides after a numbers of loops MW_Imp = input(VAR%eval(4+%eval(&m*6)),best32.);

ChrisNZ — Wed, 26 Sep 2018 03:59:35 GMT

I mean items 1 and 3 indicate an issue with the program logic and should be corrected.

Item 2 is probably why you get the issue you asked about.

Re: Macro collides after a numbers of loops MW_Imp = input(VAR%eval(4+%eval(&m*6)),best32.);

imdickson — Wed, 26 Sep 2018 05:56:07 GMT

@ChrisNZ the problem is, i dont see the problem in that datastep or source file or processing logic because if i run just 1 file or 1000 files which includes the file that were listed in the log, there wont be any error at all. What I am doing is slowly add up more and more files until i know how many files will cause the macro to throw error again.

When running the script with reading less csv, there is no error or warning to tell any logic at all. The final step of this code will append all data into one table. I can see all data display properly in the final table. Hence the confusion and headache I am having right now.

Re: Macro collides after a numbers of loops MW_Imp = input(VAR%eval(4+%eval(&m*6)),best32.);

ChrisNZ — Wed, 26 Sep 2018 06:21:50 GMT

Potential issues:

1. If no test is not true when reading WORK.OUT (data _null_; set WORK.out (obs=2);) , TCNT does not get a assigned.

I am pretty sure that's the reason for the issue as I stated before. All VARx variables must be blank or something similar.

Add the statement putlog TCNT=; just before run;

2. Once this data issue has been verified, you need to change the macro to cater for such cases.

You also need to add the missing %local statement and fix the NOTE: Numeric values have been converted to character

Your log must be clean.

3.This

set WORK.out (obs=2);
  if _n_ = 2 then do;

would be clearer as

set WORK.out (firstobs=2 obs=2);  * no more _N_ test needed;

Re: Macro collides after a numbers of loops MW_Imp = input(VAR%eval(4+%eval(&m*6)),best32.);

imdickson — Wed, 26 Sep 2018 07:30:36 GMT

@ChrisNZ I found out that the file before the one that is in log file is the actual cost due to the 2nd row of that file only has BLANK and Total. I was expecting every file to have T1 value in _N_ =2

So what i want to do is to detect if _N_ not in (T1) then skip. Is there a way to plot in to the original code below?

	data _null_;			
		set WORK.out (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;

i think i want not just to skip this loop but all other remaining if condition within the loop as this csv without T1 is considered as blank file so no need to process anything at all.

Your help is very much appreciated.

Re: Macro collides after a numbers of loops MW_Imp = input(VAR%eval(4+%eval(&m*6)),best32.);

imdickson — Wed, 26 Sep 2018 10:22:23 GMT

Hi.

I plan to use %symexist(tcnt) to find out if it exist or not. If exists, check if is zero. If it meets this criteria, output &name into dataset blankpage else continue to execute the remaining of the codes.

Here is the code that I've been thinking:

data checkcnt;
%if %symexist(tcnt) %then %do;
%put tcnt is available;
%end;
%else %do;
%put tcnt is missing;
blankfilename = "&name";

 output;
%end;
run;

in my log file, i managed to see tnct is missing. Now i am able to print tnct is missing, output the value in &name to dataset checkcnt.

Now, i want to make it to continue to process with the remaining of the code if symexist(tcnt) is true....otherwise SKIP this current iteration to the next loop and output the value in &name to dataset checkcnt.

However, my remaining code is in a data step......how should i edit it so that it can be integrated?

Re: Macro collides after a numbers of loops MW_Imp = input(VAR%eval(4+%eval(&m*6)),best32.);

imdickson — Wed, 26 Sep 2018 11:31:43 GMT

Hi, I managed to got it working....thanks everyone for the help.

Re: Macro collides after a numbers of loops MW_Imp = input(VAR%eval(4+%eval(&m*6)),best32.);

ChrisNZ — Wed, 26 Sep 2018 21:31:04 GMT

You should not be using

%if %symexist(tcnt) %then %do;

as the macro should be %local, so always exists.

Did you not fix any of the other issues I mentioned either, and lay the bed for more headaches for the person coming after you?

Re: Macro collides after a numbers of loops MW_Imp = input(VAR%eval(4+%eval(&m*6)),best32.);

Tom — Wed, 26 Sep 2018 21:57:24 GMT

I suspect that a lot of the issues will go away if you convert more of your macro code into actual SAS code. For example what are the %DO loops doing in this step that you couldn't do with data step code?

Also in addition to the logic issue of no observations matching your IF/THEN/ELSE block you need to also watch out for data steps that use CALL SYMPUTX() in code that comes after a place where the data step could stop. That is after a SET or INPUT statement. No value will be set to the macro variable when the input data set was no observations since the data step will halt when the SET statement reads past the end of the input data.

@imdickson wrote:

@ChrisNZ I found out that the file before the one that is in log file is the actual cost due to the 2nd row of that file only has BLANK and Total. I was expecting every file to have T1 value in _N_ =2

So what i want to do is to detect if _N_ not in (T1) then skip. Is there a way to plot in to the original code below?
	data _null_;			
		set WORK.out (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;	
i think i want not just to skip this loop but all other remaining if condition within the loop as this csv without T1 is considered as blank file so no need to process anything at all.

Your help is very much appreciated.

Re: Macro collides after a numbers of loops MW_Imp = input(VAR%eval(4+%eval(&m*6)),best32.);

imdickson — Thu, 27 Sep 2018 00:03:08 GMT

@ChrisNZ

i Tried running the script and it gave me the list of data that has blank _N_=2.

However, when i run it to scan through 30k of excel csv, the programme will roughly takes 2 days based on the time needed for just 1k files that took about 2 hours.

This script clearly slows down the entire process.....do you have any idea on how to improve it?

I have no choice but to skip tcnt when it doesnt exist because i will never know if any of the 30k csv has blank record or not. The file is not generated by me.

Re: Macro collides after a numbers of loops MW_Imp = input(VAR%eval(4+%eval(&m*6)),best32.);

Tom — Thu, 27 Sep 2018 00:23:53 GMT

What does the program do? Why does it need to use macro code to do it? What SAS code is the macro generating?

What do you mean by "30K of excel csv"?

Are there 30,000 different files? Just reading the list of file names will take a long time. Can you place them on a RAM disk and read from there? Or are you talking about how big one of the files is?

Are they CSV files (which are simple text files) or Excel files (which is a binary format)?

Re: Macro collides after a numbers of loops MW_Imp = input(VAR%eval(4+%eval(&m*6)),best32.);

ChrisNZ — Thu, 27 Sep 2018 00:37:01 GMT

1. Performance

I have no idea what your program does.

Two hours seems excessive for processing 1000 files, but as usual, it depends (on the files, the processing, the hardware).

7 seconds per file seems ludicrous to me.

2. You don't seem to take my advice into account much.

If you don't strive to write clean programs, you just create problems for yourself, and you'll end up here asking questions more often than necessary.

Re: Macro collides after a numbers of loops MW_Imp = input(VAR%eval(4+%eval(&m*6)),best32.);

imdickson — Thu, 27 Sep 2018 01:20:25 GMT

@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" ?