O'Reilly logo

SAS 9.4 Macro Language, 5th Edition by

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

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 447
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;
448 Appendix 5 SAS Macro Examples

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required