topic Re: Importing Datasets Using A Macro in New SAS User

Importing Datasets Using A Macro

pw7632 — Mon, 23 Dec 2019 15:29:19 GMT

I have a folder with multiple csv files that need to be imported in SAS. How can I do this using a macro? There are many files within this folder. There are also folders with only one or two csv files. Thanks for the help everyone.

Re: Importing Datasets Using A Macro

PaigeMiller — Mon, 23 Dec 2019 15:45:23 GMT

If all the CSV files have the EXACT same variable names and layout, no macro is needed. Import one using PROC IMPORT, modify the code in the log so that the the INFILE statement now uses an asterisk in place of the actual file name. This will create code that imports all the *.csv files in the folder.

Example:

infile "c:\myfolder\*.csv" /* other options as needed */ ;

If the files have different variable names or different layouts, then you might need a macro.

Re: Importing Datasets Using A Macro

pw7632 — Mon, 23 Dec 2019 15:45:32 GMT

Thats the problem I need the files loaded separately. I need this macro to import them because then I need to find the contents of each separate file.

Re: Importing Datasets Using A Macro

PaigeMiller — Mon, 23 Dec 2019 16:00:39 GMT

It's still probably easier to do it this way than write a macro. All the .csv files wind up in a single SAS data set, the FILENAME= options stores the name of the original csv file in the SAS data set, and then you can use SAS to split the data apart if that's what you need.

Example using the PROC IMPORT code written to the log as a starting point.

data mydatasetname;
     length filename $ 256;
     infile "c:\myfolder\*.csv" /* other options from PROC IMPORT log */
        filename=filename;
     /* Other statements from PROC IMPORT log */
     originalfilename=filename;
run;

Re: Importing Datasets Using A Macro

SuryaKiran — Mon, 23 Dec 2019 16:08:50 GMT

Hello,

What you can do is first read the files that are in your folder and sub-folder if applicable and have the names of the files in a dataset. Later you can run a dataset driven program as follows:

Reading the files in your folder: My examples is for Unix, but also should work for Windows after changing to correct path names (Forward slashes to backward)

%macro Files_List(dsn, /* Output Dataset name. <libref>.<dataset name> e.g: Work.Files        */
			      dir, /* Directory name(Unix/Linux only). For windows need to change slashes in below code */
			      ext  /* File extension  for search without period. i.e: sas or xlsx or txt or csv  */  
				);   
   %if %sysfunc(exist(&dsn))=0 %then %do;

	/* Create a table */
		proc sql;
		create table &dsn  
				( File_Name char(1000) format=$1000. 
				);
		quit;

   %end;
 
  %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));                                                                                                     
  /* Make sure directory can be open */                                                                                                 
  %if &did eq 0 %then %do;                                                                                                              
   %put Directory &dir cannot be open or does not exist;                                                                                
   %return;                                                                                                                             
  %end;                                                                                                                                 
                                                                                                                                        
   /* Loops through entire directory */                                                                                                 
   %do i = 1 %to %sysfunc(dnum(&did));                                                                                                  
                                                                                                                                        
     /* Retrieve name of each file */                                                                                                   
     %let name=%qsysfunc(dread(&did,&i));                                                                                               
                                                                                                                                        
     /* Checks to see if the extension matches the parameter value */                                                                   
     /* If condition is true print the full name to the log        */                                                                   
      %if %qupcase(%qscan(&name,-1,.)) = %upcase(&ext) %then %do; 
     /* Insert directory path name into table */
		proc sql;
		insert into &dsn
			set File_Name= "&dir/&name";
		quit;
      %end;                                                                                                                             
     /* If directory name call macro again */                                                                                           
      %else %if %qscan(&name,2,.) = %then %do;                                                                                          
        %Files_List(&dsn,&dir/%unquote(&name),&ext)                                                                                               
      %end;                                                                                                                             
                                                                                                                                        
   %end;   

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

/* Invoke the macro, example */
%Files_List(work.test,/user/KiranP,csv);

Now you will have the files (ending CSV ) into a dataset. Using this dataset & Proc Import you can load your csv file to SAS Datasets.

data _null_;
set test;

call execute( cat( "proc import datafile='",trim(File_Name),"' out= ",scan(scan(File_Name,-1,'/'),1,'.') ," dbms=CSV;run;"));

run;

Note: Few things you may have to take into consideration.

1) Are your csv files all Unique, if no then the dataset will be overwritten. Consider changing the dataset names in Proc Import to something Unique even though the file names are not unique.

2) Does proc import really works for you? It works on Guessing rows which you might have to consider, check for data truncation. example: if your columns first 20 rows are having length of 15 or below then proc import will consider a length of 15, but you might have records where length is >15 for later records, in this case your data is truncated to 15. You can use guessingrows=<number> as option for CSV files.

Re: Importing Datasets Using A Macro

japelin — Mon, 23 Dec 2019 16:20:56 GMT

Assuming csv has the same structure.

%let target=c:\temp;/* specify target folder including xls files. */
filename cmd pipe "dir /b &target | findstr .csv";
data _null_;
  infile cmd;
  input;
  call symputx(cats('csvf',_n_),_infile_);
  call symputx('nobs',_n_);
run;

%Macro MimportCSV;
  %do i=1 %to &nobs;
    filename csvf "&target\&&csvf&i";
    data csv_&i.;
      infile csvf dsd;
      /* describe input statement... */
    run;
    filename csvf;
  %end;
%Mend MimportCSV;
%MimportCSV;

Re: Importing Datasets Using A Macro

japelin — Mon, 23 Dec 2019 16:28:19 GMT

modidied.

%Macro MimportCSV(dir);
  filename cmd pipe "dir /b &dir | findstr .csv";
  data _null_;
    infile cmd;
    input;
    call symputx(cats('csvf',_n_),_infile_);
    call symputx('nobs',_n_);
  run;
  %do i=1 %to &nobs;
    proc import datafile="&dir\&&csvf&i"
                out=csv_&i
                dbms=CSV;
    run;
  %end;
%Mend MimportCSV;

%MimportCSV(%nrstr(c:\temp)); /* specify folder */