SAS 9.4 Macro Language, 5th Edition

Example 1: Import All CSV Files That Exist within

a Directory

Details

This example uses the IMPORT procedure to import each .csv file that resides within a

directory that is passed to the macro.

Program

%macro drive(dir,ext);

%local cnt filrf rc did memcnt name;

%let cnt=0;

%let filrf=mydir;

%let rc=%sysfunc(filename(filrf,&dir));

%let did=%sysfunc(dopen(&filrf));

%if &did ne 0 %then %do;

%let memcnt=%sysfunc(dnum(&did));

%do i=1 %to &memcnt;

%let name=%qscan(%qsysfunc(dread(&did,&i)),-1,.);

%if %qupcase(%qsysfunc(dread(&did,&i))) ne %qupcase(&name) %then %do;

%if %superq(ext) = %superq(name) %then %do;

%let cnt=%eval(&cnt+1);

%put %qsysfunc(dread(&did,&i));

proc import datafile="&dir\%qsysfunc(dread(&did,&i))" out=dsn&cnt

dbms=csv replace;

run;

%end;

%end;

%end;

%end;

%else %put &dir cannot be open.;

%let rc=%sysfunc(dclose(&did));

%mend drive;

%drive(c:\temp,csv)

Program Description

Begin the macro definition with two parameters.

%macro drive(dir,ext);

%local cnt filrf rc did memcnt name;

%let cnt=0;

Example 1: Import All CSV Files That Exist within a Directory 445

Create two macro variables:

• &FILRF will contain the fileref (Mydir) to be used within the FILENAME function.

• &RC will contain the results from the FILENAME function. The returned value is a

0 if it is successful.

The FILENAME function assigns the fileref (Mydir) to the directory passed to the macro

(&DIR).

%let filrf=mydir;

%let rc=%sysfunc(filename(filrf,&dir));

Create a macro variable named &DID that uses the DOPEN function to open the

directory. The DOPEN function returns a directory identifier value. The returned value is

0 if the directory cannot be opened.

%let did=%sysfunc(dopen(&filrf));

Use a %IF condition to make sure the directory was opened successfully.

%if &did ne 0 %then %do;

Create a macro variable named &MEMCNT that uses the DNUM function to return the

number of members within the directory.

%let memcnt=%sysfunc(dnum(&did));

Use the %DO statement to loop through the entire directory based on the number of

members returned from the DNUM function.

%do i = 1 %to &memcnt;

Create a macro variable named &NAME that uses the DREAD function to return the

name of each file. The %QSCAN function uses -1 as the second argument to start at the

back of the filename. The third argument uses the period as the delimiter. This causes the

extension of the file to be returned and to be assigned to the macro variable &NAME.

%let name=%qscan(%qsysfunc(dread(&did,&i)),-1,.);

Use the %IF statement to verify that each file contains an extension. If the file does not

contain an extension, then the contents of the %DO statement do not execute.

%if %qupcase(%qsysfunc(dread(&did,&i))) ne %qupcase(&name) %then %do;

Use the %IF statement to verify that the extension matches the second parameter that is

passed to the macro. If the condition is true, then increment a counter (&CNT) by 1. The

%PUT statement prints the full name to the log. PROC IMPORT is used to read

each .csv file that is returned and creates a data set named DSNx, where x is from the

counter named &CNT.

%if %superq(ext) = %superq(name) %then %do;

%let cnt=%eval(&cnt+1);

%put %qsysfunc(dread(&did,&i));

proc import datafile="&dir\%qsysfunc(dread(&did,&i))"

out=dsn&cnt

dbms=csv replace;

446 Appendix 5 • SAS Macro Examples