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);
I receive two errors: 1.) Insufficient authorization to access PIPE.; 2.) Error in the filename statement.
@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
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
@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-...
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.
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 2025 is scheduled for May 6-9 in Orlando, FL. Sign up to be first to learn about the agenda and registration!
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.
Ready to level-up your skills? Choose your own adventure.