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...:
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.
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
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!
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.
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 ;
if eov ;
row =0 ;
eov =0 ;
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;
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 ;
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 ......
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.