topic Re: Importing a File with the most recent Date Modified in SAS Programming

Importing a File with the most recent Date Modified

awmeyertimmy — Tue, 17 Dec 2019 16:50:34 GMT

Hello everyone,

I'm using Base SAS 9.4. I'm trying to import an Excel file into a SAS data set (which I know how to do). The problem is, there are hundreds of files in a folder and I need to import the file with the most recent Date Modified. If it helps at all, every file follows the same naming convention. Example: FileABCD_123456. The file with the maximum value of the six digits following the underscore is the file I want to import. Can anyone help? Much appreciated!!

-Adam

Re: Importing a File with the most recent Date Modified

ed_sas_member — Tue, 17 Dec 2019 17:53:38 GMT

Hi @awmeyertimmy

You can try the following code to retrieve the number of the file in the macrovariable &num_file

eg. &num_file will take the value 123456 in your example, if 123456 is the maximum value among several numbers.

NB: this code assumes the number does not exceed 8 digits.

proc sql noprint;
	select max(input(prxchange('s/^.+_//',1,trim(memname)),8.))
	into: num_file trimmed
	from dictionary.tables
	where libname = "WORK" and prxmatch('/_\d+$/',trim(memname));
quit;

proc sql;
	select memname
	from dictionary.tables
	where libname = "WORK" and scan(trim(memname),2,"_")="&num_file";
quit;

NB: please adapt your libname in the WHERE clause.

Re: Importing a File with the most recent Date Modified

Tom — Tue, 17 Dec 2019 18:26:48 GMT

Get the list of files. Parse out that numeric suffix. Find the maximum suffix. Put the name into a macro variable. Use the macro variable in place of the hard coded name in the code to read one Excel file.

The first part is easier if you know what type of operating system SAS is running on and you are allowed to run operating system commands. Otherwise you will need to resort to use the DREAD() function. (check the on-documentation for an example).

Assuming you are running on Windows you would just use the DIR command to get the list of files.

%let path=\\servername\sharename\foldername;
data files:
  infile "dir &path\*.xlsx /b" pipe truncover ;
  input filename $256. ;
  length suffix $32 number 8;
  suffix = scan(filename,-2,'._');
  number = input(suffix,??32.);
run;

proc sql noprint;
  select filename into :fname trimmed
  from files where number = max(number)
  ;
quit;

Now just use "&path/&fname" as the name of the xlsx file to convert.

Re: Importing a File with the most recent Date Modified

awmeyertimmy — Tue, 17 Dec 2019 18:42:07 GMT

I'm sorry, yes I am running Windows. When I run that code it says that I have insufficient authorization to access PIPE. So unfortunately, that isn't working for me. Thank you though!

Re: Importing a File with the most recent Date Modified

SASKiwi — Tue, 17 Dec 2019 19:06:06 GMT

Talk to your SAS administrator about enabling use of OS commands from SAS. By default this is switched off in server installations.

Re: Importing a File with the most recent Date Modified

awmeyertimmy — Tue, 17 Dec 2019 19:59:08 GMT

@ed_sas_member does that "dictionary.files" work if the files are csv files or do they need to be sas data sets. The reason I ask is that I am getting no observations (however the code executes successfully)

Re: Importing a File with the most recent Date Modified

ed_sas_member — Tue, 17 Dec 2019 20:05:42 GMT

Hi @awmeyertimmy

I am sorry, because I just realize that I made a mistake when I read your question: I thought you talked about SAS datasets and not other kind of file like csv.

To answer your question, the dictionary.tables table stores a lot of information regarding SAS datasets and is accessible via proc sql.

Re: Importing a File with the most recent Date Modified

Tom — Tue, 17 Dec 2019 20:16:55 GMT

Then you will have resort to a slightly more complex solution to get the list of files.

Make a fileref pointing to the directory. Open the directory with DOPEN(), And then read the names using DREAD(). You will need to add a test for whether the file has XLSX extension on its name or not.

%let path=\\servername\sharename\foldername;
 
data files;
  length filename $256 suffix $32 number 8;
  keep filename suffix number;
  length rc did index 8 fileref $8 ;
  rc=filename(fileref,"&path");
  did=dopen(fileref);
  do index=1 to dnum(did);
    filename=dread(did,index);
    if upcase(scan(filename,-1,'.')) = 'XLSX' then do;
      suffix = scan(filename,-2,'._');
      number = input(suffix,??32.);
      output;
    end;
  end;
run;

Re: Importing a File with the most recent Date Modified

ed_sas_member — Tue, 17 Dec 2019 20:47:26 GMT

Hi @awmeyertimmy

please have a look at @Tom ‘s answer (like below) to retrieve file names in a SAS dataset.

/* untested code */

data files (keep=Memname);
length memname $256;
	fich=filename('fich',"&path");
	did=dopen('fich');
	nb_fich=dnum(did);
	do i=1 TO nb_fich;
		memname=dread(did,i);
		output;
	end;
	rc=dclose(did);
run;

Re: Importing a File with the most recent Date Modified

awmeyertimmy — Tue, 17 Dec 2019 21:14:25 GMT

@ed_sas_member @Tom @SASKiwi Thanks everyone for your help! Much appreciated!