topic Read all csv files (without specifying the filenames) from a unix folder and create a joined dataset in SAS Programming

Read all csv files (without specifying the filenames) from a unix folder and create a joined dataset

DritanB — Fri, 12 Feb 2021 23:05:36 GMT

Hi, I have to read a folder in unix containg multiple csv files with the same structure and create a dataset with all the variables in these files. Since the file names change, I can't use these finlenames in infile statement.

Is it possible to use any macro to perform this task?

Thank you!

Re: Read all csv files (without specifying the filenames) from a unix folder and create a joined dat

Reeza — Fri, 12 Feb 2021 23:09:43 GMT

You don't need a macro (you can but it's more work) you can use wildcards.
Assuming all the files are the same you can easily read all in a single data step by wildcarding the folder.

https://communities.sas.com/t5/SAS-Communities-Library/How-do-I-write-a-macro-to-import-multiple-text-files-that-have/ta-p/223627

Re: Read all csv files (without specifying the filenames) from a unix folder and create a joined dat

DritanB — Sat, 13 Feb 2021 00:26:32 GMT

Thank you. I forgot to mention in my initial post that in the dataset that I have to create from all these csv files, I will also have to include a new variable containing the name of the csv file for each observation. Will I be able to do that by modyfying the solution you provided?

Re: Read all csv files (without specifying the filenames) from a unix folder and create a joined dat

Shmuel — Sat, 13 Feb 2021 09:53:35 GMT

Next code is designed to generate a temporary program to do what you want.

It contains few steps:

1) create a list of files to read into a sas dataset.

2) a simple macro to read one file and create a TEMP data set.

3) a dad step to generate the required code as temporary program.

it is recommended to put it to log first for checking and debug.

4) %include the temporary program to execute it

%macro dirlist(path,suffix);
	data dirlist(keep=path fname);
	    retain path "&path";
		rc=filename ("fdir","&path");  
		did=dopen("fdir");   
		if did>0 then do;    
		   num=dnum(did); 
           /* call symput('n_files',put(num,3.)); <<< not used */		   
		   do i = 1 to num;
			  fname=dread(did,i);  
			  if lowcase(scan(fname,-1,'.')) = lowcase("&suffix") 
			  then output;
		   end;
		end;
	 run;   
%mend;
%dirlist(<path>,csv);

%macro code;
   data temp;
    ... enter here your INPUT satement code to read a record ...
   run;
%mend;

%let want = <output dataset name >;
filename prog "<path>/temp_prog.sas";   
data _null_;
 set dirlist;
	 file prog;  /* for easy debug replace with put log */
     if _n_ =1 then do;
	    cmd = "proc delete data=&want; run;"; 
		put cmd;
	 end;
	 
	 cmd = "filename finp '" ||strip(path)||'/'||strip(fname)||".csv)';";
	 put cmd;
     cmd = "%code;";
	 put cmd;
	 cmd = "proc append base=&want data=temp; run;";
	 put cmd;
	 cmd = 'filename finp clear;';
	 put cmd;
run;
/** if seems ok then run next line **/
%include prog;

code was not tested. if any issue please post the log using FILE LOG instead FILE PROG.

Re: Read all csv files (without specifying the filenames) from a unix folder and create a joined dat

Kurt_Bremser — Sat, 13 Feb 2021 08:58:13 GMT

Use the FILENAME= option of the INFILE statement.

data want;
length fname filename $200;
infile "/path/*.csv" filename=fname /* other options */;
input
  .....
;
filename = fname;
run;

The two variables are necessary because the one used in the option will automatically be excluded from any output dataset.

Re: Read all csv files (without specifying the filenames) from a unix folder and create a joined dat

Reeza — Sun, 14 Feb 2021 16:36:02 GMT

The solution in the link actually does that already.