topic Re: Get names of all excel files from multiple folders in SAS Programming

Get names of all excel files from multiple folders

Emma2021 — Tue, 25 Apr 2023 17:34:46 GMT

I have multiple folder on my C drive. Within each folder, I have one or two excel files. How can I find out all the names of folders and the excel files names?

I wanted to use a macro to read those excel files into sas -how can accomplish ? Thank you.

Re: Get names of all excel files from multiple folders

ballardw — Tue, 25 Apr 2023 17:49:56 GMT

Easiest would be the Windows DIR command such as open a Windows command window and type:

Dir /S/B C:\*.xlsx

to create a TEXT file to hold that output add a ><path>\myfile.txt

at the end or similar:

Dir /S/B C:\*.xlsx > c:\folder\myfile.txt

The /S option on the command is search subfolders, so starting at C: it will search every folder on your drive. The /B option just lists the folder/file name information. The * is a wildcard for any file name with the given extension. If you have older XLS files as well as XLSX you would be best off by making two separate output files unless you are familiar with the Windows command line.

If your SAS is not server based you likely could use that as a PIPE in a filename but if your SAS is server based it likely doesn't see your hard drive to do such.

Re: Get names of all excel files from multiple folders

A_Kh — Tue, 25 Apr 2023 18:47:55 GMT

What @ballardw explained above might look like as below in code:

filename xlsxlist pipe 'dir /b /s "C:\Users\Myfolders\*.xlsx" ';
data temp;
   infile xlsxlist truncover;
   input file_path_name $200.;
   file_name= scan(file_path_name, -1, '\');
proc print;run;

Tested. Works fine for me.

Re: Get names of all excel files from multiple folders

Emma2021 — Tue, 25 Apr 2023 18:54:01 GMT

Thank you. I have the desktop version sas 9.4. Could you help with the sas code? Thanks again

Re: Get names of all excel files from multiple folders

ballardw — Tue, 25 Apr 2023 19:42:24 GMT

I would set the length of the variable to at least 256, which I think is the maximum path and filename that windows supports to avoid truncation of names.

Warning: if you have ONE-Drive installed, which typically places access in C:\users then you may be pulling names from cloud storage and not your local drive. I don't know about any other cloud services.

Re: Get names of all excel files from multiple folders

Emma2021 — Tue, 25 Apr 2023 21:34:16 GMT

Sorry what is “Dir”? My path is as follow C://documents/data/…
After this “data”, I have many sub folders with excel files

Re: Get names of all excel files from multiple folders

Reeza — Tue, 25 Apr 2023 22:16:59 GMT

Dir is a unix command. You can instead use a pre-written macro to get the list of files that you will then need to likely filter to what you want to import. Chantge the first path in the %dirtree below to be your path and run the program. Note that I've set it to run/search only to a depth of 10 folders deep. If you want it go further change the maxdepth parameter. It will create a table called filelist that will have a list of the files/folders.

filename dirtree url 
    'https://raw.githubusercontent.com/sasutils/macros/master/dirtree.sas';
%include dirtree /source2;
%dirtree(C:\documents\data\   /* Pipe delimited directory list (default=.) */
        , out=filelist  /* Output dataset name */
        , maxdepth=10 /* Maximum tree depth */);

This will contain the list of all files and folders. You can then use a data step to filter out the files that only have the xlsx extension.

data excel_file_list;
set filelist;
where type = 'F';
*test that this works,if it does uncomment the line below to filter only xlsx files;
ext = reverse(scan(reverse(filename), 1, "."));
*if ext = 'xlsx';
run;

@Emma2021 wrote:
Sorry what is “Dir”? My path is as follow C://documents/data/…
After this “data”, I have many sub folders with excel files

Re: Get names of all excel files from multiple folders

DrAbhijeetSafai — Mon, 13 Jan 2025 07:07:30 GMT

Many many thanks @ballardw !

-- Dr. Abhijeet Safai