topic Import data from several .txt files in SAS Data Management

Import data from several .txt files

USCSS_Nostromo — Mon, 22 Oct 2018 14:44:40 GMT

Hello SAS community members,

I am trying to import a single row of data from ca. 60 .txt files. I can get it to work for individual files, but when I try to import data from all files in a folder, it does not work.

My aim is to extract only two pieces of information from each of these files. The start and end clock times of a data recording. These are observations "Start Date & Time" and "End time" in the three attached .txt documents respectively (although I do not reference these variable names in the code). I need the data from row 8 only.

Once imported into SAS, I can then extract the clock times from these observations and create new variables using the SCAN function.

I am using SAS version 9.4.

Can anyone please help me get this to work for all my .txt files in a folder?

Sincerely,

Ian

Here is my code.

%let pathname=C:\;
Data RES004_AN;
infile
"C:\RES004_AN.txt"
dlm='09' firstobs=8 obs = 8 ;
input

@01Var1 1.
@02 Var2 $20.
@22Var3 $9.
@30Var4 $12.
@43 Var5 7.;

Num = Var1;
StarDatTim = Var2;
EndTim = Var3;
Dur = Var4;
DurSec = Var5;

length filename fname $256 ;
infile "&pathname\*.txt" dsd truncover filename=fname ;
array d (1000) _temporary_ ;
length dummy $8 ;
input @;
filename=scan(fname,-2,'./\') ;

drop dummy Var1 -- Var5;
RecStar =scan(StarDatTim,-1,' ');
RecEnd =scan(EndTim,-1,' ');

;run;

Re: Import data from several .txt files

TomKari — Mon, 22 Oct 2018 18:27:48 GMT

From what your post says, you have the actual "get the needed data from a file" part working, so I'm not going to consider that.

In terms of getting your data from the different datasets:

1. You need to put your successful code into a SAS macro, where the name of the dataset is a parameter, and you have some way of distinguising one output from another (an incremented counter would work fine).

2. Then you need a way to get the names of all the datasets in a directory. Take a look at the SAS "External Files" routines, they do exactly that. Here's a reference to how to use them:

http://www.mwsug.org/proceedings/2012/S1/MWSUG-2012-S128.pdf

3. Then just get the dataset names, and invoke your macro for each dataset.

Tom

Re: Import data from several .txt files

USCSS_Nostromo — Fri, 26 Oct 2018 08:28:55 GMT

Hello Tom,

Thank you for your thoughtful reply to my post. I appreciate these suggestions. My SAS skills are limited, so it will take me some to time to implement everything. I am working on it.

Best,

Ian

Re: Import data from several .txt files

AngusLooney — Sun, 28 Oct 2018 00:18:01 GMT

Neat use of those functions!

If it’s supposed in your environment, you can get more detail on the files by streaming the output of a directory listing command into a dataset, so that you can access the files’s timestamps and sizes, and also process into sub directories.

This can be useful if you want to process the files in date or size order, or the like.

You do need to have “xcmds” enabled, which is often not the case.

There is a feature where you can process all the files that match a file spec, say c:\Data\Report_*.xls, but that isn’t a great idea in a lot of circumstances, much better to know the names of the files and loop through them.

One tip is that you can incorporate the name the files as a field into each row of data, which can be useful for attribution or lineage purposes.