BookmarkSubscribeRSS Feed
🔒 This topic is solved and locked. Need further help from the community? Please sign in and ask a new question.
aquabu
Calcite | Level 5

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

1 ACCEPTED SOLUTION

Accepted Solutions
Reeza
Super User

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

7 REPLIES 7
ballardw
Super User

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.

stat_sas
Ammonite | Level 13

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.

aquabu
Calcite | Level 5

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;

Reeza
Super User

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;

aquabu
Calcite | Level 5

Thanks a ton!

That worked. I should have posted here earlier!

Thanks again!!

Reeza
Super User

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

Tom
Super User Tom
Super User

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.

sas-innovate-2024.png

Join us for SAS Innovate April 16-19 at the Aria in Las Vegas. Bring the team and save big with our group pricing for a limited time only.

Pre-conference courses and tutorials are filling up fast and are always a sellout. Register today to reserve your seat.

 

Register now!

What is Bayesian Analysis?

Learn the difference between classical and Bayesian statistical approaches and see a few PROC examples to perform Bayesian analysis in this video.

Find more tutorials on the SAS Users YouTube channel.

Click image to register for webinarClick image to register for webinar

Classroom Training Available!

Select SAS Training centers are offering in-person courses. View upcoming courses for:

View all other training opportunities.

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