This is great about the forum support, to get solutions to problems that I myself are unable to solve. I really like this forum.
Thank you all that have helped me in the past
If your CSV files have a known structure then it is easier to just read them directly into a dataset.
You can use a wildcard (*) in the file name to find all csv files in a directory.
%let dir=C:\fay\spring09 mails out;
data spring09 ;
infile "&dir\*.csv" dsd dlm=',' truncover ;
input var1 var2 .... ;
run;
You might need to add something to eliminate header lines when reading multiple files.
data spring09;
infile "&dir\*.csv" dsd dlm=',' truncover;
input @;
if _infile_ =: 'var1' then delete;
input var1 var2 ... ;
run;
The following code I wrote is a long time ago. I think you can use call execute() to optimize it.
%let subdir=D:\FTP\F-NASE09_18\; filename dir pipe "dir &subdir.*.xls /B"; data _null_; infile dir truncover end=last; input filename $100.; length f $ 8; f=filename; call symputx(cats('dsn',_n_),f); filename=cats("&subdir",filename); call symputx(cats('path',_n_),filename); if last then call symputx('nobs',_n_); run; %put _user_; %macro import; %do i=1 %to &nobs; proc import datafile="&&path&i" out=&&dsn&i dbms=excel replace; getnames=no; mixed=yes; run; %end; %mend import; %import
Ksharp
Hi,
I have similar question. But, I am trying to read multiple csv files from same folder with different file structure and files names in solaris environment. I need to check the files, and check if there are more than one row and then read the files every day, and append them.
I have created this macro to read the files. I would like to know if its possible to read all of them with a single macro.
Please advise if I am doing anything wrong in it. Thannks in Advance.
Mycode:
%let path = "/sasdata/path/";
%let libto = library;
options mprint;
%macro Readcsv (csvfile=);
/* %let hasRows=;*/
%let foundit = %sysfunc(exist(&path.&csvfile..csv));
%if &foundit %then %do;
filename csvTest "&path.&csvfile..csv";
data _null_;
infile csvTest;
input;
if _N_ eq 2 then do;
/* call symput("hasRows", "yes");*/
stop;
end;
run;
%end;
proc import datafile=&path.&csvfile..csv
out=&libto..&csvfile replace;
delimiter= ",";
getnames=yes;
scan=yes
mixed=yes;
run;
/* %end;*/
%mend Readcsv;
%Readcsv (csvfile= file_xyz);
%Readcsv (csvfile= file_abc);
If your solaris environment support FILENAME + PIPE then the task will be very simple.
But I think call execute is a good one for your question.
%let path = "/sasdata/path/";
%let libto = library;
options mprint;
%macro Readcsv (csvfile=);
/* %let hasRows=;*/
%let foundit = %sysfunc(exist(&path.&csvfile..csv));
%if &foundit %then %do;
filename csvTest "&path.&csvfile..csv";
data _null_;
infile csvTest;
input;
if _N_ eq 2 then do;
/* call symput("hasRows", "yes");*/
stop;
end;
run;
%end;
proc import datafile=&path.&csvfile..csv
out=&libto..&csvfile replace;
delimiter= ",";
getnames=yes;
scan=yes
mixed=yes;
run;
/* %end;*/
%mend Readcsv;
data _null_;
input fname $40. ;
call execute('%Readcsv(csvfile=',strip(fname),')');
cards;
file_xyz
file_abc
;
run;
Ksharp
Hello Shap,
Thannks for the suggestion. I am getting the following error when I used the call execute step. I tried different ways to accommodate the quotes, but still getting the same error. Can you please let me know if I am missing anything?
I have tried these
call execute('%Readcsv(csvfile=',strip(fname),')');
call execute('%Readcsv(csvfile=','||strip(fname)||');
/*call execute('%Readcsv(csvfile=','||strip(fname)||');');*/
ERROR 253-185: The EXECUTE subroutine call has too many arguments.
Sorry. It is my fault. I offer you the wrong code. It should use || instead of comma.
call execute('%Readcsv(csvfile='||strip(fname)||')');
or try to use cats() function:
call execute(cats('%Readcsv(csvfile=',fname,')'));
Ksharp
Are you ready for the spotlight? We're accepting content ideas for SAS Innovate 2025 to be held May 6-9 in Orlando, FL. The call is open until September 25. Read more here about why you should contribute and what is in it for you!
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.