topic Re: Importing multiple delimited txt files and saving as unique data sets in SAS Programming

Importing multiple delimited txt files and saving as unique data sets

mnguye17 — Fri, 12 Oct 2018 01:50:18 GMT

Hi,

I'm trying to import multiple TXT files from a local directory that are delimited by the character '$'. The files each have different headers and I want to output them into my work directory as separate data sets.

proc import datafile='C:\Directory\*.txt' dbms=dlm out=work.file_name;
delimiter='$';
run;

Is there a command line for the 'out=' portion of the code?

Thanks

Re: Importing multiple delimited txt files and saving as unique data sets

singhsahab — Fri, 12 Oct 2018 02:10:34 GMT

Hello Dear,

This can be accomplish by using Macro . Here you can go ...

%let path=C:\Directory;

%MACRO IMPORT_TXT(FILENAME=,OUTFILENAME=);
proc import datafile="&path.\&FILENAME..txt" dbms=dlm out=work.&OUTFILENAME replace;
delimiter='$';
run;
%MEND;

/* Calling Macro to import First file  */
%IMPORT_TXT(FILENAME=test1,OUTFILENAME=test1);

/* Calling Macro to import First file  */
%IMPORT_TXT(FILENAME=test2,OUTFILENAME=test2);

Just you have to call macro by passing parameters.

Thanks..

Re: Importing multiple delimited txt files and saving as unique data sets

Reeza — Fri, 12 Oct 2018 02:51:12 GMT

Here's a fully fledged macro. Point it to the folder and specify the extension it imports all the files. If your file names will not be valid SAS data set names you'll have to modify it to account for that.

https://documentation.sas.com/?docsetId=mcrolref&docsetTarget=n0ctmldxf23ixtn1kqsoh5bsgmg8.htm&docsetVersion=9.4&locale=en

Several other useful macro examples are here:

https://communities.sas.com/t5/SAS-Communities-Library/SAS-9-4-Macro-Language-Reference-Has-a-New-Appendix/ta-p/291716

@mnguye17 wrote:

Hi,

I'm trying to import multiple TXT files from a local directory that are delimited by the character '$'. The files each have different headers and I want to output them into my work directory as separate data sets.
proc import datafile='C:\Directory\*.txt' dbms=dlm out=work.file_name;
delimiter='$';
run;
Is there a command line for the 'out=' portion of the code?

Thanks

Re: Importing multiple delimited txt files and saving as unique data sets

mnguye17 — Fri, 12 Oct 2018 12:08:44 GMT

Thanks, I appreciate the effort!

Re: Importing multiple delimited txt files and saving as unique data sets

mnguye17 — Fri, 12 Oct 2018 13:30:54 GMT

So, I was able to run the code and it imported all my text files from the directory as individual data sets like I originally wanted. But, I'm having trouble retaining the name of the original text file in the imported data set. It simply renames to Dsn# due to this line of code

          proc import datafile="&dir\%qsysfunc(dread(&did,&i))" out=dsn&cnt

Is there a way to retain the name of the original name from the text file in the out=dsn&cnt portion?

Re: Importing multiple delimited txt files and saving as unique data sets

Tom — Fri, 12 Oct 2018 14:15:40 GMT

You cannot put it into the dataset as a variable. Not using PROC IMPORT.

Put you could put it into the dataset label.

proc import datafile="&dir\%qsysfunc(dread(&did,&i))"
 out=dsn&cnt (label="&dir\%qsysfunc(dread(&did,&i))")

If the names of the files are like XXX.txt then you might be able to parse out the XXX and use that as the name. But then your program will break if the XXX part is not valid as a dataset name.