How do I import multiple csv files into different datasets using macro

[NewbieTom]

Hi all,

I am fairly new to SAS. I am trying to import multiple csv files (all in the same format), under the same directory, and creates new dataset for each file imported. So each dataset would have the same name as the file imported , e.g. Jan.csv into Jan.sas7bdat. I did some research online and tried to copy the macro. But it did not seemed to work and I am not sure where the problem is. Thank in advance for your help.

options merror mlogic mprint symbolgen spool;
%macro drive(dir,ext);                                                                                                                  
  %local filrf rc did memcnt name i;                                                                                                    

  /* Assigns a fileref to the directory and opens the directory */                                                           
  %let rc=%sysfunc(filename(filrf,&dir));                                                                                               
  %let did=%sysfunc(dopen(&filrf));                                                                                                     

   /* Loops through entire directory */                                                                                                 
   %do i = 1 %to %sysfunc(dnum(&did));                                                                                                  

     /* Retrieve name and import each Excel file */                                                                                                   
     %let name=%qsysfunc(dread(&did,&i));    

DATA mylib.&name;
    LENGTH
        FYBK               3
        FYDBN            $ 10
        FYCOMN           $ 35
        FYCODD           $ 20
        FYCOID           $ 10
        FYSECC           $ 3;
    
    FORMAT
        FYBK             BEST1.
        FYDBN            $CHAR8.
        FYCOMN           $CHAR35.
        FYCODD           $CHAR20.
        FYCOID           $CHAR10.
        FYSECC           $CHAR3.;
    INFORMAT
        FYBK             BEST1.
        FYDBN            $CHAR8.
        FYCOMN           $CHAR35.
        FYCODD           $CHAR20.
        FYCOID           $CHAR10.
        FYSECC           $CHAR3.;
    INFILE "L:\auditing\auditing\DA\Test\2022\Data\&name"
	    ENCODING="WLATIN1"
        DLM=','
        MISSOVER
        DSD ;
    INPUT
        FYBK             : BEST1.
        FYDBN            : $CHAR8.
        FYCOMN           : $CHAR35.
        FYCODD           : $CHAR20.
        FYCOID           : $CHAR10.
        FYSECC           : $CHAR3.;
RUN;

   %end;                                                                                                                                

  /* Closes the directory and clear the fileref */                                                                                      
  %let rc=%sysfunc(dclose(&did));                                                                                                       
  %let rc=%sysfunc(filename(filrf));                                                                                                                                                                                                                                          
%mend drive;                                                                                                                            

/* First parameter is the directory of where your files are stored. */                                                                  
/* Second parameter is the extension you are looking for.           */                                                                  
%drive(L:\auditing\auditing\DA\Test\2022\Data,csv); 

Here is the log.

1                                                          The SAS System                             10:53 Friday, October 14, 2022

1          ;*';*";*/;quit;run;
2          OPTIONS PAGENO=MIN;
3          %LET _CLIENTTASKLABEL='Test(A)_1014_00b_Import_DataStep_Test';
4          %LET _CLIENTPROCESSFLOWNAME='Process Flow';
5          %LET _CLIENTPROJECTPATH='L:\auditing\auditing\DA\Test\SAS Script\ACH_10142022(A).egp';
6          %LET _CLIENTPROJECTNAME='ACH_10142022(A).egp';
7          %LET _SASPROGRAMFILE=;
8          
9          ODS _ALL_ CLOSE;
10         OPTIONS DEV=PNG;
11         GOPTIONS XPIXELS=0 YPIXELS=0;
12         FILENAME EGSR TEMP;
13         ODS tagsets.sasreport13(ID=EGSR) FILE=EGSR
14             STYLE=HtmlBlue
15             STYLESHEET=(URL="file:///C:/Program%20Files/SASHome/SASEnterpriseGuide/7.1/Styles/HtmlBlue.css")
16             NOGTITLE
17             NOGFOOTNOTE
18             GPATH=&sasworklocation
19             ENCODING=UTF8
20             options(rolap="on")
21         ;
NOTE: Writing TAGSETS.SASREPORT13(EGSR) Body file: EGSR
22         
23         GOPTIONS ACCESSIBLE;
24         /************************************************************************************************************************
24       ! *********************************
25         Purpose: Import all csv files under "Data" folder and creat a dataset ACHComb
26         Input: csv file from each month under .../Data folder
27         Output: mylib.ACHComb
28         Note:
29         1) All the date fields were read-in as character fields because the format varies; there is a separate script to convert
29       ! all these fields into proper date
30         format ("Test(A)_0928_01_Date Format" script)
31         *************************************************************************************************************************
31       ! *********************************/
32         options merror mlogic mprint symbolgen spool;
33         %macro drive(dir,ext);
34           %local filrf rc did memcnt name i;
35         
36           /* Assigns a fileref to the directory and opens the directory */
37           %let rc=%sysfunc(filename(filrf,&dir));
38           %let did=%sysfunc(dopen(&filrf));
39         
40            /* Loops through entire directory */
41            %do i = 1 %to %sysfunc(dnum(&did));
42         
43              /* Retrieve name and import each Excel file */
44              %let name=%qsysfunc(dread(&did,&i));
45         
46         DATA mylib.&name;
47             LENGTH
48                 FYBK               3
49                 FYDBN            $ 10
50                 FYCOMN           $ 35
51                 FYCODD           $ 20
52                 FYCOID           $ 10
53                 FYSECC           $ 3;
132        
133            FORMAT
134                FYBK             BEST1.
135                FYDBN            $CHAR8.
136                FYCOMN           $CHAR35.
137                FYCODD           $CHAR20.
138                FYCOID           $CHAR10.
139                FYSECC           $CHAR3.;
218            INFORMAT
219                FYBK             BEST1.
220                FYDBN            $CHAR8.
221                FYCOMN           $CHAR35.
222                FYCODD           $CHAR20.
223                FYCOID           $CHAR10.
224                FYSECC           $CHAR3.;
303            INFILE "L:\auditing\auditing\DA\Test\2022\Data\&name"
304        	    ENCODING="WLATIN1"
305                DLM=','
306                MISSOVER
307                DSD ;
308            INPUT
309                FYBK             : BEST1.
310                FYDBN            : $CHAR8.
311                FYCOMN           : $CHAR35.
312                FYCODD           : $CHAR20.
313                FYCOID           : $CHAR10.
314                FYSECC           : $CHAR3.;
393        RUN;
394        
395           %end;
396        
397          /* Closes the directory and clear the fileref */
398          %let rc=%sysfunc(dclose(&did));
399          %let rc=%sysfunc(filename(filrf));
400        %mend drive;
401        
402        /* First parameter is the directory of where your files are stored. */
8                                                          The SAS System                             10:53 Friday, October 14, 2022

403        /* Second parameter is the extension you are looking for.           */
404        %drive(L:\auditing\auditing\DA\Test\2022\Data,csv);
MLOGIC(DRIVE):  Beginning execution.
MLOGIC(DRIVE):  Parameter DIR has value L:\auditing\auditing\DA\Test\2022\Data
MLOGIC(DRIVE):  Parameter EXT has value csv
MLOGIC(DRIVE):  %LOCAL  FILRF RC DID MEMCNT NAME I
MLOGIC(DRIVE):  %LET (variable name is RC)
SYMBOLGEN:  Macro variable DIR resolves to L:\auditing\auditing\DA\Test\2022\Data
MLOGIC(DRIVE):  %LET (variable name is DID)
SYMBOLGEN:  Macro variable FILRF resolves to #LN00015
SYMBOLGEN:  Macro variable DID resolves to 1
MLOGIC(DRIVE):  %DO loop beginning; index variable I; start value is 1; stop value is 10; by value is 1.  
MLOGIC(DRIVE):  %LET (variable name is NAME)
SYMBOLGEN:  Macro variable DID resolves to 1
SYMBOLGEN:  Macro variable I resolves to 1
SYMBOLGEN:  Macro variable NAME resolves to October.csv
SYMBOLGEN:  Some characters in the above value which were subject to macro quoting have been unquoted for printing.
NOTE: Line generated by the macro variable "NAME".
404         mylib.October.csv
            ______
            22
            201
MPRINT(DRIVE):   DATA mylib.October.csv;
MPRINT(DRIVE):   LENGTH FYBK 3 FYDBN $ 10 FYCOMN $ 35 FYCODD $ 20 FYCOID $ 10 FYSECC $ 3;
SYMBOLGEN:  Macro variable NAME resolves to October.csv
SYMBOLGEN:  Some characters in the above value which were subject to macro quoting have been unquoted for printing.
MPRINT(DRIVE):   INFILE "L:\auditing\auditing\DA\Test\2022\Data\October.csv" ENCODING="WLATIN1" DLM=',' MISSOVER DSD ;
MPRINT(DRIVE):   INPUT FYBK : BEST1. FYDBN : $CHAR8. FYCOMN : $CHAR35. FYCODD : $CHAR20. FYCOID : $CHAR10. FYSECC : $CHAR3.  ;
MPRINT(DRIVE):   RUN;

ERROR 22-322: Syntax error, expecting one of the following: a name, a quoted string, /, ;, _DATA_, _LAST_, _NULL_.  

ERROR 201-322: The option is not recognized and will be ignored.

ERROR: Libref OCTOBER is not assigned.
NOTE: The SAS System stopped processing this step because of errors.
NOTE: DATA statement used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds
      

MLOGIC(DRIVE):  %DO loop index variable I is now 2; loop will iterate again.
MLOGIC(DRIVE):  %LET (variable name is NAME)
SYMBOLGEN:  Macro variable DID resolves to 1
SYMBOLGEN:  Macro variable I resolves to 2
SYMBOLGEN:  Macro variable NAME resolves to August.csv
SYMBOLGEN:  Some characters in the above value which were subject to macro quoting have been unquoted for printing.
NOTE: Line generated by the macro variable "NAME".
404         mylib.August.csv
            ______
            22
            201
MPRINT(DRIVE):   DATA mylib.August.csv;
MPRINT(DRIVE):   LENGTH FYBK 3 FYDBN $ 10 FYCOMN $ 35 FYCODD $ 20 FYCOID $ 10 FYSECC $ 3 FYCED $ 10 FYEED $ 6 FYSDJ $ 3 FYODFI 8 ;
SYMBOLGEN:  Macro variable NAME resolves to August.csv
SYMBOLGEN:  Some characters in the above value which were subject to macro quoting have been unquoted for printing.
MPRINT(DRIVE):   INFILE "L:\auditing\auditing\DA\Test\2022\Data\August.csv" ENCODING="WLATIN1" DLM=',' MISSOVER DSD ;
MPRINT(DRIVE):   INPUT FYBK : BEST1. FYDBN : $CHAR8. FYCOMN : $CHAR35. FYCODD : $CHAR20. FYCOID : $CHAR10. FYSECC : $CHAR3. ;
MPRINT(DRIVE):   RUN;

ERROR 22-322: Syntax error, expecting one of the following: a name, a quoted string, /, ;, _DATA_, _LAST_, _NULL_.  

ERROR 201-322: The option is not recognized and will be ignored.

ERROR: Libref AUGUST is not assigned.
NOTE: The SAS System stopped processing this step because of errors.
NOTE: DATA statement used (Total process time):
      real time           0.00 seconds
      cpu time            0.00 seconds
      
     

MLOGIC(DRIVE):  %DO loop index variable I is now 11; loop will not iterate again.
MLOGIC(DRIVE):  %LET (variable name is RC)
SYMBOLGEN:  Macro variable DID resolves to 1
MLOGIC(DRIVE):  %LET (variable name is RC)
MLOGIC(DRIVE):  Ending execution.
405        
406        GOPTIONS NOACCESSIBLE;
407        %LET _CLIENTTASKLABEL=;
408        %LET _CLIENTPROCESSFLOWNAME=;
409        %LET _CLIENTPROJECTPATH=;
410        %LET _CLIENTPROJECTNAME=;
411        %LET _SASPROGRAMFILE=;
412        
413        ;*';*";*/;quit;run;
414        ODS _ALL_ CLOSE;
415        
416        
417        QUIT; RUN;
418        
​

[ChrisNZ]

No need for macros.

Have you seen this https://communities.sas.com/t5/SAS-Programming/How-to-set-a-variable-filename-INFILE-statement/td-p/...?

[NewbieTom]

Thank you

[Kurt_Bremser · Accepted solution]

To get a valid dataset name, you need to remove the extension:

%let name = %qsysfunc(dread(&did.,&i.));
%let dsname = %scan(&name.,1,.);

DATA mylib.&dsname.;