BookmarkSubscribeRSS Feed
Krysia24
Obsidian | Level 7

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

 

 

 

7 REPLIES 7
Reeza
Super User
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.;
Krysia24
Obsidian | Level 7

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

 

 

jimbarbour
Meteorite | Level 14

@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

 

jimbarbour
Meteorite | Level 14

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

Kurt_Bremser
Super User

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

ballardw
Super User

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.

Reeza
Super User

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

 

 

 


 

sas-innovate-2024.png

Available on demand!

Missed SAS Innovate Las Vegas? Watch all the action for free! View the keynotes, general sessions and 22 breakouts on demand.

 

Register now!

How to Concatenate Values

Learn how use the CAT functions in SAS to join values from multiple variables into a single value.

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
  • 1138 views
  • 1 like
  • 5 in conversation