topic Re: HELP - Import Multiple txt files with Macro in SAS Programming

HELP - Import Multiple txt files with Macro

guilhermeagc — Fri, 12 Aug 2022 01:21:08 GMT

Hi everyone!

I need to import multiple files in txt format. Anyone know how i can do this?

My code to import file?

proc import file="/home/u58922560/sasuser.v94/animal01.txt"
out=work.animal01
dbms=tab
replace;
delimiter=';';
run;

Anyone can help?

Re: HELP - Import Multiple txt files with Macro

sanchit013 — Fri, 12 Aug 2022 02:28:13 GMT

Step 1 : Get list of available files in that particular folder. To achieve this, use fopen and dread function in datastep and create dataset.

Step 2: Read the each record available in this table using do loop and invoke proc import to import .txt file.

For your reference, providing some sample code. We can modify this as per your need.

/*******************************/

%macro import_file;
%let your_folder_loc = "<Enter Path here>";

filename myfldr "&your_folder_loc.";

data filenames;
length complete_filename $200;
did=dopen('myfldr');
mcount=dnum(did);

do i=1 to mcount;
complete_filename=dread(did, i);
output;
end;
rc=dclose(did);
keep complete_filename ;
run;

filename myfldr clear;

proc sql noprint;
select count(*) into :m_file_lst_cnt from filenames;
quit;

%let m_file_lst_cnt = &m_file_lst_cnt.;

%if &m_file_lst_cnt gt 0 %then
%do;

%do dm_ds_pointer=1 %to &m_file_lst_cnt.;
%local complete_filename filename_wout_ext ;

data _null_;
pointer=&dm_ds_pointer;
set filenames point=pointer;
call symput('filename_wout_ext', scan(complete_filename, 1, '.'));
call symput('complete_filename', complete_filename);
stop;
run;

%let complete_filename = &complete_filename.;
%let filename_wout_ext = &filename_wout_ext.;
filename input "&your_folder_loc./&complete_filename.";

proc import datafile=input out=&filename_wout_ext. dbms=xlsx replace;
getnames=yes;
run;
%end;
%end;
%mend import_file;

/***************************/

Re: HELP - Import Multiple txt files with Macro

Tom — Fri, 12 Aug 2022 02:31:15 GMT

Why are you using PROC IMPORT to read a text file? The DATA step can easily read a text file. In fact all PROC IMPORT does is try to GUESS what variables you want to read from the file and generates a data step for you. But that will cause a lot of headaches when you have multiple files. It could make different GUESSes on different files. Then it will be hard to combine the data or write code that uses the multiple files.

Do all of the files have the same format? The same list of variables ("columns") in the same order? If so then just write one data step to read them all.

data animals;
  length filename fname $200 ;
  infile "/home/u58922560/sasuser.v94/animal*.txt" dsd dlm='09'x truncover filename=fname;
  input @;
  if fname ne lag(fname) then delete;
  filename = scan(fname,-1,'/');
  input var1 var2 :$20. .... ;
run;

You just need to update the INPUT statement to read your actual variables. Add any LABELs to the variables you want. Attach and formats to variables that need them (most variables do NOT need to have formats attached to them).

Re: HELP - Import Multiple txt files with Macro

guilhermeagc — Fri, 12 Aug 2022 10:28:31 GMT

Hello friend! Thanks for help.

Yes, the files have the same name, bud 01, 02, 03, 04... etc
Also the same format (.txt) the same columns, at the same order.

I never made a macro before, its the first time.

Re: HELP - Import Multiple txt files with Macro

guilhermeagc — Fri, 12 Aug 2022 10:29:28 GMT

Thanks mate, i will try it.
Many thanks.

Re: HELP - Import Multiple txt files with Macro

Kurt_Bremser — Fri, 12 Aug 2022 14:59:31 GMT

@guilhermeagc wrote:
Hello friend! Thanks for help.

Yes, the files have the same name, bud 01, 02, 03, 04... etc
Also the same format (.txt) the same columns, at the same order.

I never made a macro before, its the first time.

Then there is no need for a macro, and also not PROC IMPORT. Use the data step with a wildcard.