Reading multiple CSV files with Macro, but generates only errors...

[Wouter]

Hi,

I hope anyone could help me out with the following. I want to read multiple files (from 1 till 30), placed in 1 dir, with the use of macro's (best way to do this I think). But... my version of the program only generates errors (by the way; I haven't looked yet for a solution to go over the 10, so I'll use i=1 to 9). It doesn't recognize the filename with the &i in it...:

%macro import_files;
%do i=1 %to 9;


PROC IMPORT DATAFILE= 'L:\8th\TEST REQUESTS\03 Graphs\Financieel_AB_401_Day_0&i_09_2009.csv'
OUT= work.AB_401_&i
DBMS=DLM REPLACE;
DELIMITER='3B'x;
GETNAMES=YES;
DATAROW=2;
RUN;
%end;
%mend;

%import_files

[sbb]

If you add the command below to your SAS program, I expect you will see better with the additional diagnostics -- it has to do with the syntax of your macro variable coded in the PROC IMPORT statement.

OPTIONS SOURCE SOURCE2 MACROGEN SYMBOLGEN MLOGIC;

Scott Barry
SBBWorks, Inc.

[sbb]

Also, here is suggested reading for the use of SAS macro variables.

Scott Barry
SBBWorks, Inc.

SAS Macro Language: Reference - Using Macro Variables
http://support.sas.com/documentation/cdl/en/mcrolref/61885/HTML/default/a002047074.htm

[Wouter]

However... when I change the Datafile reference to:

PROC IMPORT DATAFILE= ".....\Financieel_GV_401_Day_0&i", so ending the reference with the &i, there's no problem, and the &i resolves from 1 to 9. But I also need the remaining part of the datafile after the &i. Message was edited by: Wouter

[Wouter]

Hi,

Thanks for your input. The error statements are extended right now, and I've read some info from the site you've mentioned. It seems double quotation marks should fix the problem, but unfortunately I still encounter the same problem:

"Apparent symbolic reference I_09_2009 not resolved" and a bit further "ERROR: Physical file does not exist, L:\8th\TEST REQUESTS\03 Graphs\Financieel_AB_401_Day_0&i_09_2009.csv". I also tried to remove the blancs from the directory, but it still won't work. Then I thought maybe 2 references to i are maybe not possible, so I removed the one in the OUT= part, but still the &i does not change into 1 - 9. Do you have any idea? Thanks in advance!

[Wouter]

GOT IT!

I had to place a dot after the &i:

PROC IMPORT DATAFILE= ".......\Financieel_AB_401_Day_0&i._09_2009.csv'

Thanks for your help!

[Peter_C]

Wouter
If your CSV files all have the same structure (same number of columns and data types) and,
if you were to use a data step instead of proc import,
then you would need no macro.
All you would need is an infile statement with an * in the physical file name where your &i is appearing.
You might have to handle "start/end-of-file" to exclude headers, but not much more.
Try this
data try( compress=yes) ;
length a b c d $30 filename $100 ;
infile 'L:\8th\TEST REQUESTS\03 Graphs\Financieel_AB_401_Day_*_09_2009.csv'
dsd truncover filename= filename eov=eov;
input a -- d ;
file= filename ;
row+1 ;
output ;
if eov ;
row =0 ;
eov =0 ;
run;
It provides a quick conversion of csv info into string columns, across all csv files which conform to that name pattern, in that folder, with just the first 4 columns of each row and the file name and row number within the file.

When you examine the content, it is easy to decide
1 how big to make the columns you want to store (drop statement for the others)
2 suitable names for the columns
so you'll know adapt the length statement (and input) and
3 introduce an informat statement to make suitable conversions and
4 probably a format statement to support things like converted dates and money.

What's more, since you are doing this on a repetitive basis, these data type decisions are unlikely to change from week to week.

hope you'll find these ideas useful

of course all that was irrelevant if you just wanted an exercise to make a few mistakes and learn a little more about writing and using macros;

PeterC

[Oleg_L]

Peter,

is it possible to use your decision in SAS 9.1.3?
Or it works in SAS 9.2 only?

Oleg. Message was edited by: Oleg_1976

[Peter_C]

try it
it's worked for me for several releases

fortunately now in 9.2 (at last!), infile option TERMSTR=CRLF eliminates that error caused by excel exporting embedded line-feed characters from in-cell formatting Message was edited by: Peter.C

[Oleg_L]

Peter,
I have tried the following code. Everything is fine but firstobs option in the infile statement is not working. Why? Is it possible to avoid messages in the LOG about invalid data in the first two rows of every input file?

data test (drop=var1-var5 where=((ss_ostd not in (0 .) or ss_ostc not in (0 .)) and length(account)=20));
infile 'd:\mbm\2009\D20091106\input\*.txt' firstobs=3 dlm=';' ls=32000 truncover dsd ;
informat account $20. ss_naim $100. var1-var5 $1. ss_ostd 17. ss_ostc 17. ost_ccy 17. country $50. iso $3. client $100.
k_client $12. client_t $1. inn $char12. reg $char30. isin $char20. cb $char30.;
input account ss_naim var1-var5 ss_ostd ss_ostc ost_ccy country iso client
k_client client_t inn reg isin cb ;
run;

[Peter_C]

best advice from poster "data _null_", recommends testing the EOV condition .

before your input statement[PRE] input @ ; * loading buffer may skip to next file ;
if eov then do ;
* Have changed files if EOV has become 1 ;
input /; * input / without @ ignores current and next line;
eov = 0 ; * must reset EOV flag ;
end;[/PRE]then use your regular input statement
input account ss_naim var1-var5 ss_ostd ss_ostc ost_ccy country iso client ......

[Oleg_L]

Peter,
thank you very much.
It's perfect.
Oleg.

[data_null__]

Thank you sir.

You could also approach the problem a bit differently. Sort of, "six of one have dozen of another" or as some say "same difference".

Instead of creating an implied list of files with a wildcard filename, you can create a list files and then read them using the FILEVAR option. In the example program below the LIST of files is created by WINDOWS DIR command read from PIPE access method. The method allows the FIRSTOBS infile option to be used on each file read, EOV is not used, as you are not detecting a file change but causing it.

[pre]
data files;
length command filevar filename $256;
command = 'dir /b/s ".\acc*.sas"';

infile dummy1 pipe filevar=command length=l;
input filevar $varying256. l;
filename = filevar;
infile dummy2 filevar=filevar end=eof firstobs=3 length=l2;
do _n_ = 1 by 1 while(not eof);
input line $varying256. l2;
output;
end;
run;
[/pre]