topic Re: Pull in the last modified file in SAS Programming

Pull in the last modified file

Krysia24 — Fri, 04 Jun 2021 20:18:22 GMT

Trying to figure out how to pull in the last outputted/last modified file. Let's say I have a file called "spreadsheet.sas7bdat" in this library: "C:\" and I want to pull the last outputted "spreadsheet" file into SAS.

I am operating in a Windows environment.

I had stumbled upon this sample code but have not been successful in adapting it:

%macro lastmodifiedfile(path,MName);

filename test pipe "dir &path /od /t:w /b";
data _null_;
infile test;
input;
call symputx("&Mname.",_infile_,'g');
run;
%put Last file to be modified in &path is: &Mname.;

%mend lastmodifiedfile;
%lastmodifiedfile(/dwh_operation/sasdata/smp_production/prod_smpi2/pilot/,Fname);

Re: Pull in the last modified file

Reeza — Fri, 04 Jun 2021 20:20:43 GMT

What happens when you run the following:

%macro lastmodifiedfile(path,MName);

filename test pipe "dir &path /od /t:w /b";
data _null_;
infile test;
input;
call symputx("&Mname.",_infile_,'g');
run;
%put Last file to be modified in &path is: &Mname.;

%mend lastmodifiedfile;
%lastmodifiedfile(/dwh_operation/sasdata/smp_production/prod_smpi2/pilot/,Fname);

%put &fname.;

Re: Pull in the last modified file

ballardw — Fri, 04 Jun 2021 20:58:01 GMT

Are you trying find out about a SAS dataset in a library or an external file? Not seeing an actual immediate connection between a SAS data set and "last outputted".

If you want to know about SAS data sets what you have is not the way to go about it if you have a library defined.

Re: Pull in the last modified file

Krysia24 — Fri, 04 Jun 2021 20:58:04 GMT

I receive two errors: 1.) Insufficient authorization to access PIPE.; 2.) Error in the filename statement.

Re: Pull in the last modified file

Reeza — Fri, 04 Jun 2021 21:30:22 GMT

Assuming you have a library named demo, something like this should work. It creates a macro variable, last_modified_data that contains the name of the last modified data set you can use in your programming.

libname demo 'C:\';

proc sql noprint;
select memname into :last_modified_data from sashelp.vtable where libname = upper("demo")  having modate = max(modate);
quit;

%put &last_modified_data.

@Krysia24 wrote:

Trying to figure out how to pull in the last outputted/last modified file. Let's say I have a file called "spreadsheet.sas7bdat" in this library: "C:\" and I want to pull the last outputted "spreadsheet" file into SAS.

I am operating in a Windows environment.

I had stumbled upon this sample code but have not been successful in adapting it:

%macro lastmodifiedfile(path,MName);

filename test pipe "dir &path /od /t:w /b";
data _null_;
infile test;
input;
call symputx("&Mname.",_infile_,'g');
run;
%put Last file to be modified in &path is: &Mname.;

%mend lastmodifiedfile;
%lastmodifiedfile(/dwh_operation/sasdata/smp_production/prod_smpi2/pilot/,Fname);

Re: Pull in the last modified file

jimbarbour — Sat, 05 Jun 2021 04:06:15 GMT

@Krysia24 wrote:

I receive two errors: 1.) Insufficient authorization to access PIPE.; 2.) Error in the filename statement.

OK, that's a problem ("Insufficient authorization to access PIPE"). That means that your installation/company has turned off the ability for you to use the PIPE command as part of a file name. The "Error in the filename statement" is caused by the inability to use the PIPE command.

Have you tried using an "X" command? You could use an "X" in lieu of a Filename with the PIPE command. The "X" command can be used to run the Dir command which can be saved as a .txt file, and then you can read the .txt file.

Jim

Re: Pull in the last modified file

jimbarbour — Sat, 05 Jun 2021 04:42:24 GMT

Below is a working example using the X command. I added *.xlsx and *.xls since it sounds like you just want spreadsheets. I hope this will be helpful, but if the PIPE command is blocked, the X command may also be blocked, but you won't know that until you try it. If the X command is blocked, you might have to talk to your SAS Admin.

OPTIONS	XWAIT;
%LET	Path	=	I:\commercial\production\OPSI\documentation;
%cd(&Path);
x "dir *.xlsx,*.xls /od /t:w /b > dir_listing.txt";
FILENAME	Dir_List	"&Path\dir_listing.txt";

DATA	_NULL_;
	LENGTH	File_Name	$512.;

	INFILE	Dir_List	TRUNCOVER	END	=	_End_of_Listing;

	INPUT	File_Name	$;

	IF	_End_Of_Listing	THEN
		CALL	SYMPUTX('File_Name', File_Name, 'G');
RUN;

%PUT	NOTE:  The last spreadsheet to be modified is:  &File_Name;

The result I get is as follows. This is the correct result for my system.

NOTE: The last spreadsheet to be modified is: OPSI_Master_Dataset_List_2021-06-04.xlsx

You'll note that I'm using a macro named "cd" (change directory) in the above code. Here's the code for the macro:

/*-------------------------------------------------------------------------------*/
/*  Macro:	cd (Change Directory)
/*  Author:	Jim Barbour 
/*  Date:	11 September 2019
/*	Owner:	OptumInsight, Chart Review
/*	Info:	This macro changes the working directory at the operating system level.
/*          The macro functions in a manner similar to the "cd" command in DOS.
/*-------------------------------------------------------------------------------*/
/*									CHANGE LOG
/*-------------------------------------------------------------------------------*/
/*	Name:	Jim Barbour				Date:	11 September 2019
/*	Info:	Original implementation.
/*-------------------------------------------------------------------------------*/

%MACRO	cd(New_Dir);
	%GLOBAL	ReturnCode;

	%**	Change the working directory to a new directory.	**;
	%**	The new directory must include the path.			**;
	%LET	ReturnCode	=	%SYSFUNC(DLGCDIR("&New_Dir"));
%MEND	cd;

%**-----------------------------------------------------------------------------**;

Jim

Re: Pull in the last modified file

Kurt_Bremser — Sat, 05 Jun 2021 06:53:33 GMT

@Krysia24 wrote:

I receive two errors: 1.) Insufficient authorization to access PIPE.; 2.) Error in the filename statement.

This means that option NOXCMD is set, and you can't use the interfaces to the system (FILENAME PIPE, X, %SYSEXEC, SYSTEM function, CALL SYSTEM).

If all your files are SAS dataset files, define a library for the directory, and pull the timestamps from DICTIONARY.TABLES.

If you have other types of files, use the file functions:

FILENAME to assign a file reference to the directory

DOPEN to open the directory

in a DO loop from 1 to DNUM, use DREAD, FILENAME and FOPEN (or MOPEN) for the individual file and FINFO to retrieve the modification timestamp.

You can find examples for similar code here: https://communities.sas.com/t5/SAS-Programming/Modify-file-deletion-code-to-only-delete-files-older-than-5-days/m-p/636601#M189139