SAS Macro that lists the files at a particular directory on any FTP server (i.e. Windows Network Drive/Unix/Mainframe)...

Here's a macro that reads the filenames available at a particular directory on any FTP server (i.e. Windows Network Drive/Unix/Mainframe)...

For Windows network drives we use the Filename Pipe Statement

For Mainframe and Unix we use the FileName FTP protocol statement.

For further reference please refer to Filename statements in SAS Documentation.

First we need to create 2 Excel files

ServerList.xls – 3 columns with servertype | host | sourcedir

Ftpcredentials.xls – 3 columns host | user | pass

And then assign the physical location of these files to macrovariables fileloc and ftploc below….need to assign a name to the output dataset name to the outputdsn macro variable…

The output dataset will have the filenames | Fileattributes | builddt (i.e. timestamp)

So here’s the macro…Try it out for your self….

/*Define macro that counts obs in a SAS dataset*/

%macro RecsInDS(table,macvar);

%global &macvar;

%local handle rc;

%let handle = %sysfunc(open(&table));

%if &handle %then %do;

  %let &macvar = %sysfunc(attrn(&handle, nlobs));

  %let rc = %sysfunc(close(&handle));

%end;

%PUT RecsInDS &table: &&&macvar.    &macvar=&&&macvar.;

%mend RecsInDS;

/*Retrieve a username passwd for the Host */

%macro Ftpcredentials(ftploc,HOST);

%put;%put =============Macro: Ftpcredentials(&ftploc,&HOST);

%global user pass;      

%let user=; /* Initialize the Username */

%let pass=; /* Initialize the Password */

 data Ftpcredentials;

 length host1 $ 20;

  set UserPasswords;

  host1=compress("'"||host||"'");

  if host1="&HOST"; /*Proceed further only if the Host user/password combination is identified */

  call symput("user",compress("'"||user||"'"));

  call symput("pass",compress("'"||pass||"'"));

  run; 

%mend Ftpcredentials;

/*This Macro checks ALL the Server Locs and builds a SAS dataset with the filenames */

%macro ReadfilesfromDirectory(fileloc,ftploc,outputdsn);           

/* Read in the Server Locations Spreadsheet */

    PROC IMPORT OUT=ServerLocs

                DATAFILE= &fileloc. DBMS=EXCEL REPLACE;

                 SHEET="Sheet1$";

                 GETNAMES=YES;

                 MIXED=NO;

                 SCANTEXT=YES;

                 USEDATE=YES;

                 SCANTIME=YES;

            RUN;

/* Read in the FTP Credentials Spreadsheet */

            PROC IMPORT OUT=UserPasswords

                DATAFILE= &ftploc. DBMS=EXCEL REPLACE;

                 SHEET="Sheet1$";

                 GETNAMES=YES;

                 MIXED=NO;

                 SCANTEXT=YES;

                 USEDATE=YES;

                 SCANTIME=YES;

            RUN;

            /*Count of the number of Servers*/

            %RecsInDS(ServerLocs,num_ServerLocs2Process);

            /*create global variables for server locations */

            %do i=1 %to &num_ServerLocs2Process.;

               %global servertype&i. host&i. sourcedir&i.;;

            %end; /*close i loop;*/

            /* Create Macro variables for each of the server locations */

            data _null_; 

             set ServerLocs;

               count+1;

               cnt=put(count,4.);

                        /*MF PDS requires extra quotes for Directory eg. 'MainframeServer' */

               if servertype="MF" then call symput(compress("srcdir"||cnt),compress('"'||"'"||sourcedir||"'"||'"'));

               else if servertype="UX" then call symput(compress("srcdir"||cnt),compress("'"||sourcedir||"'"));

               else if servertype="WN" then call symput(compress("srcdir"||cnt),trim(left(sourcedir)));

               call symput(compress("servertype"||cnt),compress(servertype));             

               call symput(compress("host"||cnt),compress("'"||host||"'"));  

              run;

    /* set ftp_locs for Mainframe and Unix;*/

            %let ftp_locs=MF UX;

            /*Process this loop for every ServerLocation*/

            %do servcnt=1 %to &num_ServerLocs2Process;

              %if &&servertype&servcnt. eq WN %then

                 %do;

                                    %put;%put -------------SEARCHING FOR FILES ON SERVER LOCATION &&srcdir&servcnt.------------------------------;%put;

                                    %let dircmd1=%nrbquote(%str(dir /b /s %")%str(&&srcdir&servcnt)%str(%"));

                                    /* Use the Filename Pipe statement to return the listing of the directory */

                                    FILENAME rootloc pipe "&dircmd1"; 

                                    /*Execute the dir command and Put the results in a dataset filenames;*/

                                    data filenames;

                                                length filenm $ 200 cdir $ 800;

                                                infile rootloc truncover;

                                                input text $char1000.;

                                                filenm=scan(text,-1,'\');

                                                builddt=input(scan(text,1," "),MMDDYY10.);

                                                cdir=substr(text,1,find(text,filenm)-2);                            

                    run;          

                 %end;

                         /* if servertype is MF or UX */

              %else %if %index(&ftp_locs,&&servertype&servcnt.) gt 0 %then

                  %do; 

                %put;%put -------------SEARCHING FOR FILES ON SERVER: &&host&servcnt. AT LOCATION &&srcdir&servcnt.------------------------------;%put;

                                                %Ftpcredentials(&ftploc,&&host&servcnt); *get the username and password combo for the Host;

                                                 

                                                /* For every host location mentioned get the names of files that match our search str;*/

                                                /* Use the Filename FTP statement to return the listing of the directory */

                                                 FILENAME fname FTP "" LIST HOST=&&host&servcnt. CD=&&srcdir&servcnt. user=&user pass=&pass lrecl=32767 recfm=v debug;

                                                 /*Step 1*/

                                                 data &outputdsn;

                                                              length filenm $ 200; 

                                                             %if &&servertype&servcnt.=MF %then /* Mainframe based SRC server */

                                                              %do;

                                                               infile fname lrecl=256 pad firstobs=2; 

                                                               input @1  text $CHAR256.;  

                                                               /*put _infile_;*/

                                                               builddt=input(scan(text,3," "),YYMMDD10.);

                                                               filenm=scan(text,10," ");     

                                                              %end;

                                                              %else %if &&servertype&servcnt.=UX %then /* Unix based SRC server */

                                                              %do;

                                                                infile fname lrecl=256 pad firstobs=1; 

                                                                input @1  text $CHAR256.;

                                                                builddt=input(compress(scan(text,7," ")||scan(text,6," ")||year(today())),date9.);

                                                                filenm=scan(text,9," ");

                                                              %end;

                                                             format builddt date9.;

                                                run;

                  %end; /*end else if */

            %end; /*end servcnt loop*/

            /* Delete sensitive information from the disk */

            proc datasets lib=work nolist;

                        delete Userpasswords ServerLocs ftpcredentials;

            quit;

%mend ReadfilesfromDirectory;

/*--------------------EDIT THESE LOCATIONS ONLY -------------*/

/* Location of the Servers to Browse thru */

%let fileloc="C:\Documents and Settings\SASTechies\Desktop\ServerList.xls";

/* Location of the FTP usernames /passwords required to login to the servers mentioned above */

%let ftploc="C:\Documents and Settings\SASTechies\Desktop\ftpcredentials.xls";

/* Name of the output dataset */

%let outputdsn=filenames;

/*--------------------EDIT THESE LOCATIONS ONLY -------------*/

/* Call the Macro */

%ReadfilesfromDirectory(&fileloc,&ftploc,&outputdsn);