topic Re: Macro for importing several csv files in SAS Programming

Macro for importing several csv files

japfvg — Sun, 07 Feb 2021 10:04:40 GMT

Hi all, hope everyone is doing great!!
Again asking for your precious help
 
I've this macro for importing one single file at once that works well:
%macro Carga(File);
data work.q1;
infile &File. dlm='|' firstobs=2 missover;
input field1 : $char5.
field2 : $char5.;
run;
%mend;
I have also a dataset containing all the csv files in a folder (the dataset is called "work.files" and the field with the csv file names is called "filename") which I need to load with the previous macro, I'm trying with this code but it's not working:
data _null_;
set work.files;
call execute(cats('%nrstr(%Carga(',filename,'))'));
run;
This last code was recomended by some of you guys for another problem (the parameters were date type instead of filenames) and worked very well so I was trying to create a similar code for this new challenge.
 
Any help would be greatly appreciated.
 
Thanks in advance.
 
Regards to all!!
 
Fer
 

Re: Macro for importing several csv files

Kurt_Bremser — Sun, 07 Feb 2021 09:46:50 GMT

Your macro has a parameter called File which you never use in the macro.

Instead you use a macro variable called Archivo, which is not set in your code (as posted).

Your macro also uses a fixed name for the dataset, so after using the macro N times, only the Nth import will be stored in dataset q1. You need to devise a way of either feeding the dataset name to the macro via a second parameter, or to create a (valid) dataset name from the infile name within the macro.

So let's bo back to square one: post the data step code that successfully imports a single file (without any macro code), and sufficient examples for the contents of your dataset files.

Re: Macro for importing several csv files

japfvg — Sun, 07 Feb 2021 10:09:02 GMT

Thanks Kurt for the comment, I just changed the macro code with the same variable...

And you're right, there is another part of the code which will append all the CSV files but it has not been written yet so for now it will take only the last file.

The second code it's what's not working with now and what's giving me trouble at this point.

Thanks again

Fer

Re: Macro for importing several csv files

Kurt_Bremser — Sun, 07 Feb 2021 11:26:41 GMT

If your goal is the import of all csv files into a single dataset, then no macro is needed. You can use your files dataset to supply filenames to the INFILE statement with the FILEVAR= option.

Re: Macro for importing several csv files

singhsahab — Sun, 07 Feb 2021 14:38:40 GMT

@japfvg Below code may help you

%LET MAIN_PATH=KEEP PATH HERE;

DATA have;
 RC=FILENAME("FILEREF","&MAIN_PATH.");
 MYDIR=DOPEN("FILEREF");
 IF MYDIR > 0 THEN 
    NUM=DNUM(MYDIR);
	 DO I=1 TO NUM;
			IF I > 0 THEN DO
			   FILE_NAME=DREAD(MYDIR,i);
			   OUTPUT;
	END;
	END;
	rc=dclose(mydir);
RUN;
DATA have1;
SET have;
extn=STRIP(SCAN(FILE_NAME,-1,'.'));
if extn eq 'csv';
RUN;
PROC SQL ;
SELECT FILE_NAME INTO :SUBFILE1 -  FROM HAVE1;
%LET N=&SQLOBS;
QUIT;
%LET DATA=DATASETNAME;

%MACRO CSV_ALL;
%DO I=1 %TO &N.;
PROC IMPORT DATAFILE="&MAIN_PATH\&&SUBFILE&I"
DBMS=CSV REPLACE OUT=&DATA&I;
RUN;
%END;
%MEND;

%CSV_ALL;

Re: Macro for importing several csv files

Tom — Sun, 07 Feb 2021 15:10:37 GMT

No need for macro code. Just use a single data step to read all of the text files at once.

data WANT;
  set work.files;
  fname = filename ;
  infile text dsd  dlm='|' truncover firstobs=2 filevar=fname end=eof;
  do while(not eof);
    input field1 :$5. field2 :$5. field3 ;
    output;
  end;
run;

Re: Macro for importing several csv files

japfvg — Tue, 09 Feb 2021 05:36:02 GMT

Thanks to all for your quick responses!!

I tested the filevar option using this:

data work.q1;
set work.files1;
fname = filename;
infile text filevar=fname end=eof dlm=';' firstobs=2 dsd missover;

do while(not eof);
	input
		eoddate : ddmmyy11.	
		numberx : $char10.
		stgeneral : $char4.
end;

run;

Dataset work.files1 contains two records of two csv files which are needed to be imported.

After running the whole code, for some reason, the input with the filevar option is reading only the first record of every csv file.

Any idea why is happening this?

Thanks again for all your support.

Fer

Re: Macro for importing several csv files

Kurt_Bremser — Tue, 09 Feb 2021 08:40:22 GMT

Look closely at codes posted. Your code misses the explicit OUTPUT statement in the DO loop, so you only have one implicit output at the end of the data step iteration, outputting the last record read from a file.