41880 - Read all files from a directory and create separate SAS® data sets with unique names

Sample 41880: Read all files from a directory and create separate SAS® data sets with unique names

The sample code on the Full Code tab shows how to read all files in a directory and create separate SAS data sets with unique names. Use the PIPE engine in the FILENAME statement to access the directory information. Then, use macro code with a %DO loop to execute a DATA step separately to read each of all files in the directory. To view the SAS log showing the execution of the sample program, click the Results tab.

This sample assumes that there are four comma-delimited files in the folder C:\_today\. The names are:

   file1.csv, file2.csv, file3.csv, and file4.csv.

The first DATA step is used to get the directory information to create a data set that contains each full pathname of all the files with a .csv extension.

The next DATA step uses _NULL_ in the DATA statement to avoid creating a SAS data set since the purpose of this step is to create macro variables. In the SET statement, the END= option is used to create a variable that indicates when you have reached the end of a data set. A variable called COUNT is created to increment each time an observation is read to keep track of how many files there are. The first CALL SYMPUTX creates macro variables that each contains the full path and name of each file to read. The COUNT variable is used to create separate macro variables for each path and name combination. The second CALL SYMPUTX creates macro variables that each contains the name of the file that is used in the DATA statement to create a unique SAS data set for each file read. The last macro variable is created after all observations of the path and names have been read in order to place the total count of files that will be read into a macro variable called MAX.

The macro is created so that a separate DATA step is run for each file to be read from the directory. This is controlled by the macro %DO loop. The DATA statement uses the macro variable with the %DO loops index variable I to use each of the separate macro variables created that contain the unique data set names. The INFILE statement uses the %DO loops index variable also except the separate macro variables created contain each path and name. Because this sample is reading comma delimited files, the DSD option and the TRUNCOVER option are used in the INFILE statement. If the input files have records longer than 256 bytes, you might need to use the LRECL= option in the INFILE statement to increase the size of the input buffer, depending on your release of SAS.

These sample files and code examples are provided by SAS Institute Inc. "as is" without warranty of any kind, either express or implied, including but not limited to the implied warranties of merchantability and fitness for a particular purpose. Recipients acknowledge and agree that SAS Institute shall not be liable for any damages whatsoever arising out of their use of this material. In addition, SAS Institute will provide no support for the materials contained herein.

In order to run the sample code below, first create the folder C:\_today\. In that folder, create two or more sample CSV files named File1.csv, File2.csv, and so on. You need to create only one record in each file with three comma-separated values.

filename DIRLIST pipe 'dir "C:\_today\file*.csv" /b ';

data dirlist ;
   infile dirlist lrecl=200 truncover;
   input file_name $100.;
run;

data _null_;
   set dirlist end=end;
   count+1;
   call symputx('read'||put(count,4.-l),cats('c:\_today\',file_name));
   call symputx('dset'||put(count,4.-l),scan(file_name,1,'.'));
   if end then call symputx('max',count);
run;

options mprint symbolgen;
%macro readin;
   %do i=1 %to &max;

      data &&dset&i;
         infile "&&read&i" lrecl=1000 truncover dsd;
         input var1 $ var2 $ var3 $;
       run;

   %end;
%mend readin;

%readin;

These sample files and code examples are provided by SAS Institute Inc. "as is" without warranty of any kind, either express or implied, including but not limited to the implied warranties of merchantability and fitness for a particular purpose. Recipients acknowledge and agree that SAS Institute shall not be liable for any damages whatsoever arising out of their use of this material. In addition, SAS Institute will provide no support for the materials contained herein.

785  filename DIRLIST pipe 'dir "C:\_today\file*.csv" /b ';
786
787  data dirlist ;
788  infile dirlist lrecl=200 truncover;
789  input file_name $100.;
790  run;

NOTE: The infile DIRLIST is:
      Unnamed Pipe Access Device,
      PROCESS=dir "C:\_today\file*.csv" /b,RECFM=V,
      LRECL=200

NOTE: 4 records were read from the infile DIRLIST.
      The minimum record length was 9.
      The maximum record length was 9.
NOTE: The data set WORK.DIRLIST has 4 observations and 1 variables.
NOTE: DATA statement used (Total process time):
      real time           0.05 seconds
      cpu time            0.01 seconds


791
792  data _null_;
793  set dirlist end=end;
794  count+1;
795  call symputx('read'||put(count,4.-l),cats('c:\_today\',file_name));
796  call symputx('dset'||put(count,4.-l),scan(file_name,1,'.'));
797  if end then call symputx('max',count);
798  run;

NOTE: There were 4 observations read from the data set WORK.DIRLIST.
NOTE: DATA statement used (Total process time):
      real time           0.00 seconds
      cpu time            0.01 seconds


799
800  options mprint symbolgen;
801  %macro readin;
802  %do i=1 %to &max;
803
804   data &&dset&i;
805   infile "&&read&i" lrecl=1000 truncover dsd;
806   input var1 $ var2 $ var3 $;
807   run;
808
809  %end;
810  %mend readin;
811
812  %readin;
SYMBOLGEN:  Macro variable MAX resolves to 4
SYMBOLGEN:  && resolves to &.
SYMBOLGEN:  Macro variable I resolves to 1
SYMBOLGEN:  Macro variable DSET1 resolves to file1
MPRINT(READIN):   data file1;
SYMBOLGEN:  && resolves to &.
SYMBOLGEN:  Macro variable I resolves to 1
SYMBOLGEN:  Macro variable READ1 resolves to c:\_today\file1.csv
MPRINT(READIN):   infile "c:\_today\file1.csv" lrecl=1000 truncover dsd;
MPRINT(READIN):   input var1 $ var2 $ var3 $;
MPRINT(READIN):   run;

NOTE: The infile "c:\_today\file1.csv" is:
      Filename=c:\_today\file1.csv,
      RECFM=V,LRECL=1000,File Size (bytes)=2021,
      Last Modified=02Dec2015:16:01:30,
      Create Time=02Dec2015:16:01:30

NOTE: 101 records were read from the infile "c:\_today\file1.csv".
      The minimum record length was 16.
      The maximum record length was 23.
NOTE: The data set WORK.FILE1 has 101 observations and 3 variables.
NOTE: DATA statement used (Total process time):
      real time           0.02 seconds
      cpu time            0.01 seconds


SYMBOLGEN:  && resolves to &.
SYMBOLGEN:  Macro variable I resolves to 2
SYMBOLGEN:  Macro variable DSET2 resolves to file2
MPRINT(READIN):   data file2;
SYMBOLGEN:  && resolves to &.
SYMBOLGEN:  Macro variable I resolves to 2
SYMBOLGEN:  Macro variable READ2 resolves to c:\_today\file2.csv
MPRINT(READIN):   infile "c:\_today\file2.csv" lrecl=1000 truncover dsd;
MPRINT(READIN):   input var1 $ var2 $ var3 $;
MPRINT(READIN):   run;

NOTE: The infile "c:\_today\file2.csv" is:
      Filename=c:\_today\file2.csv,
      RECFM=V,LRECL=1000,File Size (bytes)=2021,
      Last Modified=02Dec2015:16:01:30,
      Create Time=02Dec2015:16:01:30

NOTE: 101 records were read from the infile "c:\_today\file2.csv".
      The minimum record length was 16.
      The maximum record length was 23.
NOTE: The data set WORK.FILE2 has 101 observations and 3 variables.
NOTE: DATA statement used (Total process time):
      real time           0.02 seconds
      cpu time            0.01 seconds


SYMBOLGEN:  && resolves to &.
SYMBOLGEN:  Macro variable I resolves to 3
SYMBOLGEN:  Macro variable DSET3 resolves to file3
MPRINT(READIN):   data file3;
SYMBOLGEN:  && resolves to &.
SYMBOLGEN:  Macro variable I resolves to 3
SYMBOLGEN:  Macro variable READ3 resolves to c:\_today\file3.csv
MPRINT(READIN):   infile "c:\_today\file3.csv" lrecl=1000 truncover dsd;
MPRINT(READIN):   input var1 $ var2 $ var3 $;
MPRINT(READIN):   run;

NOTE: The infile "c:\_today\file3.csv" is:
      Filename=c:\_today\file3.csv,
      RECFM=V,LRECL=1000,File Size (bytes)=2221,
      Last Modified=02Dec2015:16:01:30,
      Create Time=02Dec2015:16:01:30

NOTE: 101 records were read from the infile "c:\_today\file3.csv".
      The minimum record length was 19.
      The maximum record length was 23.
NOTE: The data set WORK.FILE3 has 101 observations and 3 variables.
NOTE: DATA statement used (Total process time):
      real time           0.01 seconds
      cpu time            0.01 seconds


SYMBOLGEN:  && resolves to &.
SYMBOLGEN:  Macro variable I resolves to 4
SYMBOLGEN:  Macro variable DSET4 resolves to file4
MPRINT(READIN):   data file4;
SYMBOLGEN:  && resolves to &.
SYMBOLGEN:  Macro variable I resolves to 4
SYMBOLGEN:  Macro variable READ4 resolves to c:\_today\file4.csv
MPRINT(READIN):   infile "c:\_today\file4.csv" lrecl=1000 truncover dsd;
MPRINT(READIN):   input var1 $ var2 $ var3 $;
MPRINT(READIN):   run;

NOTE: The infile "c:\_today\file4.csv" is:
      Filename=c:\_today\file4.csv,
      RECFM=V,LRECL=1000,File Size (bytes)=1925,
      Last Modified=02Dec2015:16:01:30,
      Create Time=02Dec2015:16:01:30

NOTE: 101 records were read from the infile "c:\_today\file4.csv".
      The minimum record length was 17.
      The maximum record length was 23.
NOTE: The data set WORK.FILE4 has 101 observations and 3 variables.
NOTE: DATA statement used (Total process time):
      real time           0.01 seconds
      cpu time            0.00 seconds

Date Modified:	2019-06-11 14:56:29
Date Created:	2010-12-16 13:14:05

Product Family	Product	Host	SAS Release
Product Family	Product	Host	Starting	Ending
SAS System	Base SAS	Windows 7 Ultimate 32 bit
		Windows 7 Professional x64
		Windows 7 Professional 32 bit
		Windows 7 Home Premium x64
		Windows 7 Home Premium 32 bit
		Windows 7 Enterprise x64
		Windows 7 Enterprise 32 bit
		Microsoft Windows XP Professional
		Microsoft Windows Server 2008 for x64
		Microsoft Windows Server 2008
		Microsoft Windows Server 2003 for x64
		Microsoft Windows Server 2003 Standard Edition
		Microsoft Windows Server 2003 Enterprise Edition
		Microsoft Windows Server 2003 Datacenter Edition
		Microsoft Windows NT Workstation
		Microsoft Windows 2000 Professional
		Microsoft Windows 2000 Server
		Microsoft Windows 2000 Datacenter Server
		Microsoft Windows 2000 Advanced Server
		Microsoft Windows 95/98
		OS/2
		Microsoft® Windows® for x64
		Microsoft Windows XP 64-bit Edition
		Microsoft Windows Server 2003 Enterprise 64-bit Edition
		Microsoft Windows Server 2003 Datacenter 64-bit Edition
		Microsoft® Windows® for 64-Bit Itanium-based Systems
		Windows 7 Ultimate x64
		Windows Millennium Edition (Me)
		Windows Vista
		Windows Vista for x64
		64-bit Enabled AIX
		64-bit Enabled HP-UX
		64-bit Enabled Solaris
		ABI+ for Intel Architecture
		AIX
		HP-UX
		HP-UX IPF
		IRIX
		Linux
		Linux for x64
		Linux on Itanium
		OpenVMS Alpha
		OpenVMS on HP Integrity
		Solaris
		Solaris for x64
		Tru64 UNIX

Support

Sample 41880: Read all files from a directory and create separate SAS® data sets with unique names

Operating System and Release Information