topic Re: Reading a filename from a dataset in SAS Procedures

Reading a filename from a dataset

aquabu — Mon, 07 Jul 2014 20:38:05 GMT

Hi all.

I am really new to SAS and so far am enjoying every bit of learning the language.

I need help with a small piece of code that I am trying to write to automate a process. What I am trying to do is read the contents of a .txt file in a dataset. the file should have the latest timestamp.

I am able to get the filename (with the latest timestamp) in the latest_file dataset. I want to be able to extract the filename from the dataset and read the contents of the file in a new dataset.

Appreciate all your help.

I have spent over 2 days trying to get this work.

SAS code attached.

/* Code snippet below */

data latest_file;

set all_file_sorted (firstobs=1 obs=1);

run;

filename file1;

proc sql noprint;

select flnm into :file1 from latest_file where _n_=1;

quit;

data WORK.AB_TEST;

%let _EFIERR_ = 0; /* set the ERROR detection macro variable */

infile "file1" delimiter = '|' MISSOVER DSD lrecl=32767 firstobs=2 ;

/* Code snippet End */

TIA

Best Regards

Re: Reading a filename from a dataset

ballardw — Mon, 07 Jul 2014 20:46:04 GMT

You don't have an external file associated with file1 and the syntax infile "file1" is going to look for file relative to where SAS is executing that is named file1.

if SAS is executing with a default location like C:\users\username\AppData\Local\Temp then that is the location FILE1 needs to be in. If you use a fully qualified file path as you do with INDAT that might solve the problem but then the syntax is infile File1 without quotes.

Re: Reading a filename from a dataset

stat_sas — Mon, 07 Jul 2014 20:47:08 GMT

Not sure, if you put &file1 instead of file1 in

infile "file1" delimiter = '|' MISSOVER DSD lrecl=32767 firstobs=2 ;

and remove %let _EFIERR_ = 0; /* set the ERROR detection macro variable */

from the code to see if it works.

Re: Reading a filename from a dataset

Reeza — Mon, 07 Jul 2014 20:54:02 GMT

This isn't valid code in SQL, either switch it to a data step or you'll need to change it to appropriate SQL.

proc sql noprint;

select flnm into :file1 from latest_file where _n_=1;

quit;

Here's the data step equivalent. This creates a macro variable &file1 that you don't seem to be using anywhere.

data _null_;

set latest_file;

if _n_=1 then call symput('file1', flnm);

run;

%put &file1.;

Re: Reading a filename from a dataset

aquabu — Mon, 07 Jul 2014 20:54:04 GMT

Thanks for your replies.

Ok my bad. I will rephrase my original question again.

What i want to achieve is get the filename from 'latest_file' and use that to read the file in WORK.AB_TEST.

The full code is below:

** 'filepath' is the file directory storing the file(s) which will be renamed;

%LET filepath= %NRBQUOTE(C:\sasdata\datatest\);

** 'delimiter' is the delimiter used in filenames;

%LET delimiter= %NRBQUOTE(_);

** 'file_extension' is the file extension of the external data file(s);

%LET file_extension= %NRBQUOTE(.txt);

FILENAME indat PIPE "dir ""&filepath.*&file_extension"" ";

DATA all_file;

INFILE indat TRUNCOVER;

INPUT fldt $ 1-11 fltm $ 12-22 flsiz $ 25-38 flnm $ 39-100;

RUN;

PROC SQL;

CREATE TABLE all_file_info AS

SELECT *

FROM all_file

WHERE NOT (INDEXC(fldt,'Check','Directory')>0 OR MISSING(fldt));

RUN;

PROC SORT DATA=all_file_info OUT=all_file_sorted ;

BY flsiz ;

RUN ;

data latest_file;

set all_file_sorted (firstobs=1 obs=1);

run;

PROC PRINT DATA=latest_file ;

RUN ;

data WORK.AB_TEST;

%let _EFIERR_ = 0; /* set the ERROR detection macro variable */

infile <HOW DO I GET THE FILENAME HERE> delimiter = '|' MISSOVER DSD lrecl=32767 firstobs=2 ;

...

Run;

Re: Reading a filename from a dataset

Reeza — Mon, 07 Jul 2014 20:56:33 GMT

data _null_;

set latest_file;

if _n_=1 then call symput('file1', flnm);

run;

%put &file1.;

data WORK.AB_TEST;

%let _EFIERR_ = 0; /* set the ERROR detection macro variable */

infile "&file1" delimiter = '|' MISSOVER DSD lrecl=32767 firstobs=2 ;

...

Run;

Re: Reading a filename from a dataset

aquabu — Mon, 07 Jul 2014 21:01:12 GMT

Thanks a ton!

That worked. I should have posted here earlier!

Thanks again!!

Re: Reading a filename from a dataset

Tom — Tue, 08 Jul 2014 03:06:42 GMT

So you have a dataset with a variable that contains the name of the file that you want to read to create a new dataset? You do not have to use a macro variable in that case. You can use the FILEVAR option on the INFILE statement to tell it that the name of the file to read is already in a dataset variable.

data ab_test ;

if _n_=1 then set latest_file;

infile dat filevar=flnm delimiter = '|' TRUNCOVER DSD lrecl=32767 firstobs=2 ;

....

P.S. Use TRUNCOVER instead of MISSOVER and save yourself a lot of trouble when reading variable length lines.