Help using Base SAS procedures

Reading a filename from a dataset

Accepted Solution Solved
Reply
Occasional Contributor
Posts: 10
Accepted Solution

Reading a filename from a dataset

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

Attachment

Accepted Solutions
Solution
‎07-07-2014 04:56 PM
Super User
Posts: 17,840

Re: Reading a filename from a dataset

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;

View solution in original post


All Replies
Super User
Posts: 10,504

Re: Reading a filename from a dataset

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.

Trusted Advisor
Posts: 1,204

Re: Reading a filename from a dataset

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.

Occasional Contributor
Posts: 10

Re: Reading a filename from a dataset

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;

Solution
‎07-07-2014 04:56 PM
Super User
Posts: 17,840

Re: Reading a filename from a dataset

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;

Occasional Contributor
Posts: 10

Re: Reading a filename from a dataset

Thanks a ton!

That worked. I should have posted here earlier!

Thanks again!!

Super User
Posts: 17,840

Re: Reading a filename from a dataset

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.;

Super User
Super User
Posts: 6,502

Re: Reading a filename from a dataset

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.

☑ This topic is SOLVED.

Need further help from the community? Please ask a new question.

Discussion stats
  • 7 replies
  • 277 views
  • 8 likes
  • 5 in conversation