BookmarkSubscribeRSS Feed
Wouter
Obsidian | Level 7
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
12 REPLIES 12
sbb
Lapis Lazuli | Level 10 sbb
Lapis Lazuli | Level 10
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
Lapis Lazuli | Level 10 sbb
Lapis Lazuli | Level 10
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
Obsidian | Level 7
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
Obsidian | Level 7
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
Obsidian | Level 7
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
Rhodochrosite | Level 12
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
Obsidian | Level 7
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
Rhodochrosite | Level 12
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
Obsidian | Level 7
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
Rhodochrosite | Level 12
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
Obsidian | Level 7
Peter,
thank you very much.
It's perfect.
Oleg.
data_null__
Jade | Level 19
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]

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

Discussion stats
  • 12 replies
  • 3102 views
  • 0 likes
  • 5 in conversation