list of files within a folder and inserting EXCEL hyperlinks

[SASuserlot]

I came across an old SAS article how to list the files in folder in excel with hyperlinks. I am not familiar with the first part of the code using the 'PIPEDIR' and also the code based on UNIX operating system.  If I can  I modify the  first part  (part 1)  of the code I can make it useful for the Windows operating system. Any help greatly appreciated. Thanks.

Reference link of article: https://www.lexjansen.com/pharmasug-cn/2017/CC/PharmaSUG-China-2017-CC06.pdf

*********************************************************************
UNIX BASED OS CODE:
PART 1;

*** Access the directory information to get the list of files contained within the target folder;
%LET target_folder= xxx;
FILENAME pipedir PIPE " ls -l &target_folder./*.* ";
DATA _sm_filenames;
INFILE pipedir TRUNCOVER LRECL=5000;
INPUT line $char1000.;
RUN;

**************************************************************************

*** Retrieve valuable information as separate variables and create the Microsoft EXCEL hyperlinks;
DATA filelist;
SET _sm_filenames;
FORMAT hyper_link filenames type time size adjsize $200. pos len RE 6.0;
IF _N_ = 1 THEN
RE = PRXPARSE('/(?<=\d)\s+\w{3}\s+\d{1,2}\s+[0- 9\:]{4,5}\s+(?=\/sasmeta)/');
RETAIN RE;
CALL PRXSUBSTR(RE,strip(line),pos,len);
	time=substr(strip(line),pos,len);
	filenames=scan(strip(line),-1,'/');	
	type= scan(strip(filenames),-1,'.');
	size=scan(strip(line),5, ' ');
	adjsize=strip(put(input(size,best.)/1024,20.0))!!'KB';
	hyper_link='=HYPERLINK('!!'"'!!strip(filenames)!!'"' !! ', "'!!'Link'!!'")';
IF strip(filenames) ne '_directory_master_file.xls' AND upcase(strip(type)) in ('PDF' 'XLS' 'XLSX');
KEEP filenames Type time adjsize hyper_link;
LABEL filenames = 'File name'
	type = 'Type'
	time = 'Date of creation or last modification*'
	adjsize = 'Size'
	hyper_link = 'HyperLink'
;
RUN;

PROC SORT DATA=filelist;
BY filenames;
RUN;

*** Create a Microsoft EXCEL file of the list;
ODS _ALL_ CLOSE;
ODS tagsets.excelxp options( embedded_titles = 'yes' embed_titles_once = 'yes'
embedded_footnotes = 'yes' autofit_height = 'yes' skip_space = '1,0,0,1,1'
autofilter = "all" default_column_width = "15" Absolute_Column_Width= "15"
sheet_name = "Hyperlink to each file")
FILE= "&target_folder./_directory_master_file.xls" STYLE=LISTING;
PROC REPORT DATA =filelist NOWD MISSING SPACING=1 HEADLINE HEADSKIP SPLIT="@";
COLUMN hyper_link filenames type time adjsize;
DEFINE hyper_link / STYLE = [COLOR=bib FONTWEIGHT= bold] ;
RUN;
ODS tagsets.excelxp CLOSE;
ODS LISTING;

[ballardw]

Big caveat: The code like this will pretty much only run on a stand alone installation OR where the drive where the files are stored is accessible by the machine running SAS. In many instances these days you are connecting to SAS running on a server that cannot see your local drive at all. So all paths have to reference things the SERVER sees. So your path might end up including machine information as well as drives.

If you only need the file names then change

FILENAME pipedir PIPE " ls -l &target_folder./*.* ";

to

FILENAME pipedir PIPE "Dir &target_folder.\*.* /B ";

should work. You want to make sure that the macro variable (target_folder) includes the full path starting at a drive. Something like:

%let target_folder=C:\users\myname\subfolder;

The /B switch for DIR command just shows filenames. Reference the DIR help for any other switches that might be wanted. There are 15 document switches for DIR that affect content of the results, order and some selection criteria.

Or you can use SAS functions DOPEN, DINFO, FINFO and related functions to use just SAS functions to pull the same information.

[Patrick]

@SASuserlot To create the Excel with the hyperlinks I'd be using the more modern ODS EXCEL.

I've had lately a similar question for which @Ksharp provided the solution here.