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
Don't miss out on SAS Innovate - Register now for the FREE Livestream!
Can't make it to Vegas? No problem! Watch our general sessions LIVE or on-demand starting April 17th. Hear from SAS execs, best-selling author Adam Grant, Hot Ones host Sean Evans, top tech journalist Kara Swisher, AI expert Cassie Kozyrkov, and the mind-blowing dance crew iLuminate! Plus, get access to over 20 breakout sessions.
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.